Convert Excel column letter to number with Power Query

Question

Using Power Query M language, how can I convert Excel style column letter into its corresponding column number?

Examples:
A=1
Z=26
AA=27
AZ=52
SZ=520
XFD=16384

I have a Power Query script that is reading the .xml inside Excel .xlsx files. It is returning the cells that contain color but the returned values are in the AZ123 format. I am able to separate the column from the row, but now I want to have the column converted to a number.

I already know how to convert the column letter to a number using a VBA function using ColumnNumber = Range(ColumnLetters & "1").Column, but I want to have the processing done in Power Query.

Here (VBA) and here (C#, JS) are some algorithm examples in other languages but I don't know how to write that in M.

Bonus request: Can you write it as an M function so it's easily reusable?

Here's a [link](https://www.excelguru.ca/forums/showthread.php?7047-Extract-Cell-Color-with-M&p=28875&viewfull=1#post28875) to the script I started with that can identify color cells from Excel: That's beyond the scope of this question. Just imagine a power query that has "AA" in a column and I want to have another column that converts that into 27. — Ben, Dec 18 '19 at 16:36

score 3 · Accepted Answer · answered Dec 18 '19 at 16:38

You should be able to do this with some list functions in a calculated column:

List.Accumulate(
    List.Transform(
        Text.ToList([ColumnLetters]),
        each Character.ToNumber(_) - 64
    ), 0,
    (state, current) => state*26 + current
)

Let's see how this works with an example:

Text.ToList("XFD") = {"X", "F", "D"}

List.Transform({"X", "F", "D"}, each Character.ToNumber(_) - 64)
    = {88-64, 70-64, 68-64}
    = {24, 6, 4}

Note: For the Character.ToNumber offset of 64, you need to be sure the letters are upper case.

List.Accumulate({24, 6, 4}, 0, (state, current) => state*26 + current)
    = ((0*26 + 24)*26 + 6)*26 + 4
    = 4 + 6*26 + 24*26^2
    = 16348

Note: List.Accumulate recursively steps through the list applying the rules you specify at each element.

Can you explain why you noted that `List.Accumulate` uses recursion? Is there a risk of high memory usage? — Ben, Dec 18 '19 at 16:49
I'm just explaining what it's doing. The method is quite efficient memory-wise. — Alexis Olson, Dec 18 '19 at 16:50

Convert Excel column letter to number with Power Query

1 Answers1