topic Formula - Convert a letter to a number e.g. A=1,B=2, Z=26, AA=27 in Alteryx Designer Desktop Discussions

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

DS80845 — Tue, 08 Sep 2020 10:38:30 GMT

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

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

MichaelLaRose — Tue, 08 Sep 2020 13:11:49 GMT

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

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

DS80845 — Tue, 08 Sep 2020 13:32:03 GMT

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

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

jdunkerley79 — Tue, 08 Sep 2020 14:33:01 GMT

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

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

DS80845 — Tue, 08 Sep 2020 16:01:22 GMT

@jdunkerley79

Brilliant!! Thanks a lot!!!

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

DataGuy77 — Wed, 10 Aug 2022 17:33:41 GMT

How about the reverse i have numbers i want to convert Letter

A-Z = 0-25

AA = 26

AB = 27

AC = 28...etc?

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

dsandmann — Fri, 11 Nov 2022 23:37:04 GMT

Hey @DataGuy77 - I had a use case for this so ended up creating something that would revert the digits based on the answer given by @jdunkerley79. The formula itself probably isn't the most condensed possible, but it is functional. It used modulo and some other basic math to get the conversions by letter.

I included two options, one that keeps things relatively simple, and then a nested version for the return col and all previously used data in the event you just want to use one more complex formula, although the simplified version is definitely more readable.

I had to use if statements to account for Z's (modulo would return a zero for them) and at the end where the value is 0, the char from int tool returns an @ symbol for the value of 64, so I just replace it with a blank.

Since your digits are starting at zero, you should just need to add 1 to your values for it to work within this. A is 1, AA is 27 etc.