Answered

Trying to convert int to alphabetical letter

(0) Share

Report

Posted on by Wes_G

I used the Format Data by Examples tool to try to describe what I want to do. A screenshot is below, but basically I need to convert numbers to letters such that 1 would be "a", 2 would be "b" and so on. When I get to 27, it should do "aa" and 28 should be "ab".

I'm not sure if I'm giving bad examples to the tool or what, but if anybody knows what I should use for a formula to accomplish this please let me know!

Categories:

General topics

I have the same question (0)

All responses (4)

Answers (1)

David_MA 13,908 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!
I think you may be looking for an expression such as this:

replace(replace(replace(replace(replace(replace(replace(replace(replace(variables('inputNumber'), '0', 'a'), '1', 'b'), '2', 'c'), '3', 'd'), '4', 'e'), '5', 'f'), '6', 'g'), '7', 'h'), '8', 'i'), '9', 'j')

If the number is 012 it should return abc. Replace variables(inputNumber') with your dynamic field data. And of course, just adjust what letter you want each number replaced with.

Was this reply helpful? Yes No
Verified answer

SamLed 2,338 Moderator on at

Like (1)

Report
Copy link

Link copied!

Hi Wes_G,

Create an array to store all letters :
nb : Started with "#" so "a" has index 1

then use this expression :

if(greater(variables('InputNumber'),702),
if(greater(variables('InputNumber'),1378), '#',concat(variables('Alphabet')[sub(int(first(split(string(div(variables('InputNumber'),26)),'.'))), if(equals(mod(variables('InputNumber'),26),0),27,26))],
variables('Alphabet')[sub(int(first(split(string(div(sub(variables('InputNumber'), 702),26)),'.'))),if(equals(mod(variables('InputNumber'),26),0),0,-1))],
variables('Alphabet')[add(sub(sub(variables('InputNumber'), 702), mul(26, int(first(split(string(div(sub(variables('InputNumber'), 702),26)),'.'))))),if(equals(mod(variables('InputNumber'),26),0),26,0))])),
if(less(variables('InputNumber'),27), variables('Alphabet')[variables('InputNumber')], concat(variables('Alphabet')[sub(int(first(split(string(div(variables('InputNumber'),26)),'.'))),if(equals(mod(variables('InputNumber'),26),0),1,0) )], variables('Alphabet')[add(sub(variables('InputNumber'), mul(26, int(first(split(string(div(variables('InputNumber'),26)),'.'))))),if(equals(mod(variables('InputNumber'),26),0),26,0))])))

Here to run with 20 & 79 :

This expression works for number from 1 to 1378 ('a' to 'zzz') else it returns "#"
______________________________________________________________
If I have answered your question, please Accept the post as solution.
If you like my response, please Thumbs Up.

Was this reply helpful? Yes No
Wes_G 15 on at

Like (0)

Report
Copy link

Link copied!

I just saw this reply but thank you so much this is a perfect solution. Do you think it would be a quick fix to add numbers greater than 702? If I can support up to 'zzz' then I don't think I'll ever have to change this flow again! If it complicates the logic by more than one or two steps, I shouldn't need that extra functionality.

Was this reply helpful? Yes No
SamLed 2,338 Moderator on at

Like (1)

Report
Copy link

Link copied!

You challenged me, took me a while to find, but here's one that should do the trick, its limit is 1378 ('zzz'):

if(greater(variables('InputNumber'),702),
if(greater(variables('InputNumber'),1378), '#',concat(variables('Alphabet')[sub(int(first(split(string(div(variables('InputNumber'),26)),'.'))), if(equals(mod(variables('InputNumber'),26),0),27,26))],
variables('Alphabet')[sub(int(first(split(string(div(sub(variables('InputNumber'), 702),26)),'.'))),if(equals(mod(variables('InputNumber'),26),0),0,-1))],
variables('Alphabet')[add(sub(sub(variables('InputNumber'), 702), mul(26, int(first(split(string(div(sub(variables('InputNumber'), 702),26)),'.'))))),if(equals(mod(variables('InputNumber'),26),0),26,0))])),
if(less(variables('InputNumber'),27), variables('Alphabet')[variables('InputNumber')], concat(variables('Alphabet')[sub(int(first(split(string(div(variables('InputNumber'),26)),'.'))),if(equals(mod(variables('InputNumber'),26),0),1,0) )], variables('Alphabet')[add(sub(variables('InputNumber'), mul(26, int(first(split(string(div(variables('InputNumber'),26)),'.'))))),if(equals(mod(variables('InputNumber'),26),0),26,0))])))

Edited solution with this formula
______________________________________________________________
If I have answered your question, please Accept the post as solution.
If you like my response, please Thumbs Up.

Was this reply helpful? Yes No