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

K

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
 
F

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

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)
 
R

Rick Rothstein

This formula should work...

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

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
 
F

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
 
R

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).
 
R

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top