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

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

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

"Karto" wrote in message
...
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

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)

--
Biff
Microsoft Excel MVP


"Karto" wrote in message
...
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

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" wrote:

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

"Karto" wrote in message
...
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

.

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

"Karto" wrote in message
...
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" wrote:

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

"Karto" wrote in message
...
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

.

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/micro...747caeed555a69

--
Rick (MVP - Excel)


"Karto" wrote in message
...
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

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

--
Biff
Microsoft Excel MVP


"Rick Rothstein" wrote in message
...
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/micro...747caeed555a69

--
Rick (MVP - Excel)


"Karto" wrote in message
...
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

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.

--
Rick (MVP - Excel)


"T. Valko" wrote in message
...
So, how were you able to make this reply but the others are "lost in
space" ?

--
Biff
Microsoft Excel MVP


"Rick Rothstein" wrote in message
...
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/micro...747caeed555a69

--
Rick (MVP - Excel)


"Karto" wrote in message
...
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