Excel - letter substitution code

Posted by Constantly Confused PM | on Tue, 06/03/2012 - 16:22 1174 5 comments | report

Say each letter of the alphabet is assigned a number (a=1, b=2 etc...), is there any (simple) way to make it so if I type 'cat' it shows 3120?

I can imagine how to do it if I type c a t in seperate cells, but any way I can do it with a string of text?

Comments

you could ...

ShirleyM | Tue, 06/03/2012 - 16:48 | Permalink

Put a table on a different worksheet then use text handling (left, mid, right, etc with variables), vlookup, and concatenate to assemble the replacement, but I am sure someone will know a trick or two that will make it much simpler :)

Thanks

Constantly Confused | Tue, 06/03/2012 - 16:56 | Permalink

ShirleyM wrote:

use text handling (left, mid, right, etc with variables),

I got everything but that bit... lol I'll read up on that!

The function utility of Excel will guide you

ShirleyM | Tue, 06/03/2012 - 19:00 | Permalink

Look up text in the functions, but instead of using numbers to pull out the single characters use variables. Do a LEN function to get the maximum no of characters in the source field.

Another idea

ShirleyM | Tue, 06/03/2012 - 20:16 | Permalink

Maybe you could use 'replace' or 'substitute', and if the numbers are not fixed maybe you could use the Ascii codes for those letters.

Dont forget ...

JC | Wed, 07/03/2012 - 10:44 | Permalink

The ASCII character set already has numbers assigned to characters - http://www.asciitable.com/

Assuming base 1 and not base zero

Cell C2 = ABC then apply:

=CODE(MID(C2,1,1))-64 & CODE(MID(C2,2,1))-64 & CODE(MID(C2,3,1))-64 (for upper case subtract 64 if base 1)

Answer = 123

Your problem will be knowing how many characters the user types; the length & where to stop the ... & CODE(MID(C2,3,1))-64

This may help:

http://blogs.technet.com/b/hub/archive/2011/03/29/formula-watch-convert-to-and-from-ascii-codes-in-excel.aspx

Add comment