CAT = 593, Concatenate and Add

[mgirvin]

Dear Smartest Excelers In The World,

In cell A1 I have the letter C

In cell A1 I have the letter A

In cell A1 I have the letter T

In the range E1:E26 I have the letters A to Z

In the range F1:F26 I have some numbers from 1 to 9 that are associated with each letter. For example, the number 5 is associated with C, the number 9 is associated with A, and the number 3 is associated with T.

I have used this formula to go from C-A-T in cells A1, A2, and A3 to the number 593 (I used the key strokes Ctrl + Shift + Enter to enter formula):

=MCONCAT(LOOKUP(A1:A3,E1:E26,F1:F26))+0

Is there a better way to do this (better = shorter formula)?

 

[Richard Schollar]

Howdy Mike

This is an alternative (not saying it's better though ):

=SUM(LOOKUP(A1:A3,E1:F26)*10^(3-ROW(A1:A3)))

Confirmed with Ctrl+Shift+Enter

 

[Dave Patton]

try minor revision to your formula

=--MCONCAT(LOOKUP(A1:A3,E1:F26))

 

[rconverse]

What is the -- in the beginnig for?

 

[mgirvin]

Dear RichardSchollar,

Your formula is so good! What a great pair of glasses you had on when you looked at this problem. I like it because it does not require that I use a MoreFuc, AND:

The “10^(3-ROW(A1:A3))” is such a great Excel method for doing what we did in 4th grade when we learned to multiply! I will definitely be adding the “10^(3-ROW(A1:A3))” trick to my Excel toolbox.


Dear Dave Patton,

Your formula is also good! I like that you have simplified LOOKUP to two arguments and used the double negative.


Dear rconverse,

The double negative replaces the +0; both convert the text to a number.


Hanging out at the Mr Excel Message Board is better than eating candy (or drinking beer)!