Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

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

DS80845
7 - Meteor

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
6 REPLIES 6
MichaelLaRose
10 - Fireball

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

DS80845
7 - Meteor

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
ACE Emeritus
ACE Emeritus

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

 

 

DS80845
7 - Meteor

@jdunkerley79 

 

Brilliant!! Thanks a lot!!!

DataGuy77
7 - Meteor

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

A-Z = 0-25

AA = 26

AB = 27

AC = 28...etc?

dsandmann
8 - Asteroid

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.

 

 

 

 

Labels