Excel - letter substitution code | AccountingWEB

# 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?

### you could ...

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

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

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

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

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)