can i assign a numerical value to a letter(s)?

Are these individual letters, each in its own cell? If so, a simple two column lookup table can be used to assign the numbers.

If the letters are part of a string of text, the problem is a bit more difficult. Here's an example, used to test for "dollar words," words whose letters, numbered by their position in the alphabet, sum to exactly 100.

The original was in AppleWorks, and I didn't bother to deal with Numbers's complaint (the blue 'warning' triangles) that I was using a boolean statement as a number.

You can see where this, or something similar, could get a bit unwieldy with a long string.

So, before going further, could you provide a mere detailed description of what you want Numbers to do. A screen shot showing an example, including the expected results, would be useful.

Regards,

Barry

Characters are already assigned numberical values called ASCII codes. Numbers allows you to get the ASCII code using the CODE() function and to perform the opposite operation of translating a character code to a character using the CHAR(). these actually work with Unicode (longer values to allow for extended characters).

you can see a more in-depth desription of ASCII here: http://www.asciitable.com/

If you want your own custom translation of a character to a number you canmake a table that contains the characters and you translation then look up the value. If the table is simple enough you can perform the translation using a formula.

Here is my cut at what you want to do:

For the table "Entry":

C1="The Sum of the string is "&SUM(Characters :: 2:2)

For the table "Characters":

A1=MID(Entry :: $A$2, COLUMN(), 1)

B1=IFERROR(CODE(A1), "")

select A1 and B1, then drag the little circle at the bottom right of the selection to the right to fill

You enter the string in Table "Entry" in cell B1

I hope this helps

Hi Gary,

Here's a second screen shot with some revisions. Click for larger view. Formulas included below.

Test word:

Row 1 accepts the entry word or phrase in A1, then separates it into it's individual characters, starting in B1.

A1: Enter the word or string here

B1 (and Fill right to end of table): =MID($A1,COLUMN()-1,1)

Row 2 retrieves the ASCII code for each character (A = 65), converts it to the value for the upper case version of the letter if necessary, and subtracts 64 from the result (so that A or a = 1, B or b = 2, etc. This is the version used in AppleWorks, where forcing a boolean expression to act as a number did not generate the warning triangle shown in the rows in this cell.

B2 (and Fill right to end of table): =IF(B1>"",CODE(B1)-(32*(CODE(B1)>92))-64,"")

Submitted mostly for interest. It worked fine for my purposes, but it doesn't trap non-letter characters (eg. a space), so may not give the results you expect.

Row 3 uses a Lookup function (HLOOKUP in this case) to retrieve values for each letter from a Lookup table. This version, with the associated lookup table, is case insensitive, and ignores characters other than the letters in the top row of the table. The numbers in the second row are those I entered. If you want a different set of values, it's easy to edit—just enter the number you want to be substituted for each letter.

C2 (and Fill right to end of table): =IFERROR(HLOOKUP(B1,Letter value lookup :: $1:$2,2,FALSE),"")

The FALSE argument at the end specifies an 'exact-match'. Non-letter characters (or cells with no character at all) in row 1 cause a 'not found' error, trapped by IFERROR, and placing a null string in the cell.

A2: =IF(LEN($A$1)>COLUMNS(1:1)-1,"Too many letters",SUM(B2:P2))

A3: =IF(LEN($A$1)>COLUMNS(2:2)-1,"Too many letters",SUM(B3:P3))

Each provides the SUM of values in the cells in its own row (starting in column B). If the word/phrase contains more characters than there are available columns, it presents the message "Too many letters"

Letter Count:

This table provides a count of each of the letters used in the word/phrase in the main table, Test word.

A2 (and Filled right): =COUNTIF(Test word :: $1:$1,A1)

Letter value lookup:

No formulas. All values entered directly.

Regards,

Barry