Excel - letter substitution code

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
ShirleyM's picture

you could ...

ShirleyM | | 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 :)

Constantly Confused's picture

Thanks

Constantly Confused | | 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!

ShirleyM's picture

The function utility of Excel will guide you

ShirleyM | | 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.

ShirleyM's picture

Another idea

ShirleyM | | 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 | | 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
Log in or register to post comments