Formula - Convert a letter to a number e.g. A=1,B=2, Z=26, AA=27

DS80845

Hi All

Anyone know how to convert a letter to a number in Alteryx

e.g. A=1, B=2, Z=26, AA=27 etc?

In excel the formula is =Column(Indirect(String&1))

Thanks for your help

Regards,

Damiano

Expression

Accepted answers

MichaelLaRose

Hi @DS80845,

If you were only looking to convert individual letters you could use the function CharToInt() in order to get the unicode number of the character. You could then loop over the string to get the number for each character and then construct a number that represents that string. However, it will not do exactly what you asked below.

Since you say you are looking to call AA 27 I put the following workflow together. You could use this to build a lookup table and join on the Letter field to get the ID number.

You can add additional sets of tools to expand the tool out to 4 or more characters. The dataset will get large quickly so if you need particularly long strings this may not be a viable path.

Best,

Michael

Numeric Values for individual and multiple letters.yxmd

jdunkerley79

Hi @DS80845 ,

If you want to do it as a single expression then try:

(CharToInt(Substring(PadLeft([Letter], 3, CharFromInt(64)), 2, 1)) - 64) + 
(CharToInt(Substring(PadLeft([Letter], 3, CharFromInt(64)), 1, 1)) - 64) * 26 +
(CharToInt(Substring(PadLeft([Letter], 3, CharFromInt(64)), 0, 1)) - 64) * 26 * 26

Have attached a quick sample based on @MichaelLaRose work

Excel Column Formula.yxmd

All comments

MichaelLaRose

Hi @DS80845,

Since you say you are looking to call AA 27 I put the following workflow together. You could use this to build a lookup table and join on the Letter field to get the ID number.

You can add additional sets of tools to expand the tool out to 4 or more characters. The dataset will get large quickly so if you need particularly long strings this may not be a viable path.

Best,

Michael

Numeric Values for individual and multiple letters.yxmd

DS80845

Thanks @MichaelLaRose !!

I was hoping for just a formula but that would do too.

Thank you very much for helping! Much appreciated.

Kind Regards,

Damiano

jdunkerley79

Hi @DS80845 ,

If you want to do it as a single expression then try:

(CharToInt(Substring(PadLeft([Letter], 3, CharFromInt(64)), 2, 1)) - 64) + 
(CharToInt(Substring(PadLeft([Letter], 3, CharFromInt(64)), 1, 1)) - 64) * 26 +
(CharToInt(Substring(PadLeft([Letter], 3, CharFromInt(64)), 0, 1)) - 64) * 26 * 26

Have attached a quick sample based on @MichaelLaRose work

Excel Column Formula.yxmd

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703