Convert numbers to Excel Column Letters in Power Query

Question

I would like to transform Numbers to Excel Column Letters.

There are several threads on the topic but it seems that it has not been answered yet for M language.

I managed to address it with the following code but I am sure someone will have a much more efficient code to propose:

= Table.AddColumn(Source, "Column Letter", each Text.Combine({ if (Number.IntegerDivide(
    Number.IntegerDivide(
        Number.IntegerDivide([Column1]-1,1),26)-1,26))<1 then "" else Character.FromNumber( 64+Number.IntegerDivide(Number.IntegerDivide(Number.IntegerDivide([Column1]-1,1),26)-1,26)),
(if Number.IntegerDivide([Column1]-1,26)-
Number.Abs(
    Number.IntegerDivide(
        Number.IntegerDivide([Column1]-1,26)
        -1,26)*26)<1 then "" else 
        Character.FromNumber(64+
        Number.IntegerDivide([Column1]-1,26)-Number.Abs(
            Number.IntegerDivide(
                Number.IntegerDivide([Column1]-1,26)-1,26)*26))),
    (if [Column1]-Number.Abs(
    Number.IntegerDivide([Column1]-1,26)*26)<1 then "" else Character.FromNumber(64+[Column1]-Number.Abs(
    Number.IntegerDivide([Column1]-1,26)*26)))}))

Thank you!

Ron Rosenfeld · Accepted Answer · 2022-04-26 18:40:45Z

1

I think this custom function will work also:

Edit
*Formula for b corrected as per @VassilisAgapitos note below

(n as number)=>

let 
//make sure it is an integer
    a = Number.IntegerDivide(n,1),
    
    b = Number.IntegerDivide(Number.IntegerDivide(a-1,26)-1,26),
    L1 = if b = 0 then null else Character.FromNumber(b+64),

    c = Number.IntegerDivide(a-b*26*26-1,26),
    L2 = if c = 0 then null else Character.FromNumber(c+64),
    
    d = a-b*26*26-c*26,
    L3 = Character.FromNumber(d+64) 
in  
    Text.Combine({L1,L2,L3})

You can use it as a Table.AddColumn argument:

 #"Added Custom" = Table.AddColumn(#"Changed Type", "Column Letter", each fnNumberToExcelColumn([Column Number]), type text)

edited Apr 26, 2022 at 18:40

answered Apr 22, 2022 at 19:48

Ron Rosenfeld

62k7 gold badges35 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Vassilis Agapitos Over a year ago

Thank you, Ron, that's a very neat and clean function, highly appreciated! It needs only a small correction for b: b = Number.IntegerDivide(Number.IntegerDivide(a-1,26)-1,26) Otherwise it gives wrong answer for 1353 and similar cases.

Ron Rosenfeld Over a year ago

@VassilisAgapitos Thanks for picking that up. Missed it during my testing. Answer edited with credit.

horseyride · Accepted Answer · 2022-04-22 20:48:08Z

0

An uglier version than Ron's, just for fun

=(if [Column1] <703 then "" else Character.FromNumber(64+Number.IntegerDivide([Column1]-27,26*26)))
&( if [Column1] >26 then Replacer.ReplaceText(Character.FromNumber(64+ Number.Mod( Number.IntegerDivide([Column1]-1,26) ,26) ), "@", "Z") else "" )
&(Character.FromNumber(65+Number.Mod([Column1]-1,26)))

edited Apr 22, 2022 at 20:48

answered Apr 22, 2022 at 20:20

horseyride

21.7k3 gold badges15 silver badges26 bronze badges

3 Comments

Vassilis Agapitos Over a year ago

Thank you @horseyride, I am keeping the solution in case I need to express it without a function, very efficient too, kudos!!

horseyride Over a year ago

feel free to make it a function!

Vassilis Agapitos Over a year ago

True! I like the fact that it remains short and efficient even inside an added column command.

Collectives™ on Stack Overflow

Convert numbers to Excel Column Letters in Power Query

2 Answers 2

2 Comments

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Related