CAT = 593, Concatenate and Add

mgirvin

Well-known Member
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)?

Excel Facts

Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Richard Schollar

MrExcel MVP
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

Well-known Member
try minor revision to your formula

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

rconverse

Well-known Member
What is the -- in the beginnig for?

mgirvin

Well-known Member
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)!

Replies
3
Views
173
Replies
7
Views
643
Replies
6
Views
255
Replies
3
Views
270
Replies
6
Views
273

1,191,034
Messages
5,984,260
Members
439,880
Latest member
Mpozzi59

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back