Take 3 digits from Text and turn into numberical value

[unclejemima]

I have a catalog that lists a series of names for product.

I would like to create a numerical formula that can give the text name a number using the first 3 letters.

If A=01, B=02...Z=26 (lower case would be same)

Example, ALVIANO Series would be 011222 using the letters ALV to get the number code. I'm only interested in the first 3 letters.

Please and thank you,

[Kevin UK]

Hi unclejemima

Try using the VLOOKUP, see the attach sheet.

[Jarko28]

Hi,

Just realized Kevin posted a solution. He's another variation with no lookup table...A1 is the name in all capital characters....

Please Login or Register  to view this content.

Let me know if this is what you were after.

thanks.

[Ghozi Alkatiri]

try it
=TEXT(COLUMN(INDIRECT(MID(LEFT(A1,3),1,1)&1)),"00")&TEXT(COLUMN(INDIRECT(MID(LEFT(A1,3),2,1)&1)),"00")&TEXT(COLUMN(INDIRECT(MID(LEFT(A1,3),3,1)&1)),"00")

[unclejemima]

Epic. You guys rock. Thank you all! Another solved thread

[Kevin UK]

Hi unclejemima

No problem & thanks for the feed back. Another version, assuming your data is in A1.

Please Login or Register  to view this content.