Need Help with Alpha into Numeric expressions (not ASCII/ANSI/Etc.

[Karto]

Hola everyone,

I am trying to convert Alpha into a Numeric response so that depending on
what Letter is typed in one field, a Numeric response is returned in another.

ex:
if A1 = A or B then A3 = 1
if A1 = C or D or E then A3 = 2
if A1 = F or G then A3 = 3
if A1 = H or I or J then A3 = 4
if A1 = K or L then A3 = 5
if A1 = M or N or O then A3 = 6
if A1 = P or Q then A3 = 7
if A1 = R or S or T then A3 = 8
if A1 = U or V then A3 = 9
if A1 = W or X or Y or Z then A3 = 0
(basically a modified telephone keypad)

Thanks for any help

 

[Fred Smith]

I would use Vlookup for this. Create a table with your starting letter (A,
C, F, H, K, M, P, R, U, W) in, say E1:E10, and the numbers 1 through 0 in
F1:F10.

In a3, enter:
=vlookup(a1,E1:F10,2,true)

Regards,
Fred

 

[T. Valko]

Create a 2 column table like this:

A...1
C...2
F...3
H...4
K...5
M...6
P...7
R...8
U...9
W...0

Assume that table is in the range D1:E10.

Then...

=LOOKUP(A1,D1:E10)

 

[Rick Rothstein]

This formula should work...

=RIGHT(INT((SEARCH(A1,"AB ,CDE ,FG ,HIJ ,KL ,MNO ,PQ ,RST ,UV ,WXYZ")+4)/5))

 

[Karto]

Thanks you oh so much,... being new to this, Arrays still manage to elude my
grasp of full understanding, but I got it to work by modifying your reply...
I changed it to: =IF(B3="","",VLOOKUP(B3,B10:C19,2,TRUE)) to get rid of the
N/A error when the input is blank.

Basically I am taking the first 7 characters of a business name & dropping
them into B3:H3 (1 char per field, I knew that typing the whole name in 1
field & plucking out the needed chars in order was well beyond my skills)

then displaying those 7 chars in B5:H5 in numerical form from a (modified)
telephone keypad.
ex: B3:H3 = P I Z Z A H U,
I wanted B5:H5 to respond with "7 4 0 0 1 4 9"

Thanks a ton

 

[Fred Smith]

Glad to help. Thanks for the feedback.

To separate the first 7 characters of a name, use:
=left(a1,1)
=mid(a1,2,1)
=mid(a1,3,1)
=mid(a1,4,1)
=mid(a1,5,1)
=mid(a1,6,1)
=mid(a1,7,1)

You can then combine these formulas to save cells, so in B5, you'd have:
=if(left(a1,1)="","",vlookup(left(a1,1),b10:c19,2,true))
B6: =if(mid(a1,2,1)="","",vlookup(mid(a1,2,1),b10:c19,2,true))
etc.

Now you can enter the name in one cell to get your results.

Regards,
Fred

 

[Rick Rothstein]

Let me try one more time...

I sent you a response a little while ago, but I am not seeing it in my
newsreader, so I don't know if you can see it or not. If you can see it,
there is a minor problem with the formula... if A1 is empty, it will
incorrectly evaluate to 1. Use this formula instead...

=RIGHT(INT((SEARCH(A1,"AB ,CDE ,FG ,HIJ ,KL ,MNO ,PQ ,RST ,UV
,WXYZ")+4)/5))

Now this formula assumes only letters (upper or lower case) will be in A1.
If A1 can contain other entries besides single letters, then use this
formula instead...

=IF(AND(LEN(A1)=1,ISNUMBER(SEARCH(A1,"ABCDEFGHIJKLMNOPQRSTUVWXYZ"))),
RIGHT(INT((SEARCH(A1,"AB ,CDE ,FG ,HIJ ,KL ,MNO ,PQ ,RST ,UV
,WXYZ")+4)/5)),"")

By the way, you should probably copy/paste the above formulas into the
formula bar, rather than trying to type it in, so you can get the spacing
correct (there are always four characters delimited by each comma).

 

[Rick Rothstein]

For those wanting to see the responses that I can't seem to post here, Biff
discovered (and told me about it in a private email) that Google Groups has
it archived even though it won't show up here. I have no idea how that can
be, but it is. Here is the link...

http://groups.google.com/group/micr...79747caeed555a69?hl=en&tvc=1#79747caeed555a69

 

[T. Valko]

So, how were you able to make this reply but the others are "lost in space"
?

 

[Rick Rothstein]

The problem appears to be in some combination of text that I used in my
other posts... each of my prior attempts contained the same quoted text
(because that is what I wanted the OP to see), so each was rejected because
of whatever reaction that particular text combination triggered in the
Microsoft servers... here, I didn't quote any of that other text, so my
response went through as a normal posting.