How to iterate through the alphabet like Excel does

Hi, I am really frustrated about that issue, because it seems to me, that it is really easy, but I dont get it running...

So I try to write some cells in an Excel sheet, in a specific range. The range is supplied to the xlswrite or writetable function in the Excel style, i.e. A1:AB15. I would like to create a string that specifies the range dynamically. So I need a function, that converts numbering (1,2,3 ...) to "excel alphabetical numbering" (A,B,C, ..., Z, AA, AB...). I tried a lot around with some base 26 and base 27 stuff. But it still doesn't give me satisfying results. In the end, I don't really care anymore about the Excel table numbering stuff, i just want to get this function alphabetStr = num2ExcelAlpha(num) to produce the correct string.

My initial thoughts were: The alphabet is some kind of base 26 enumeration (sorry for the wrong terms but I am an engineering grad, not a maths grad...). So basically I can get the number of digits of my "alphabetical number" with some log to the base 26 stuff. It didn't work for me.

In the end I am counting the digits kind of "manually". I bet there is something easier with logarithms, but I didn't figure it out.

digits = 0;
while num > 0
    num = num - 26^(digits+1);
    digits = digits+1;
end

Now, that we have the number of digits, I would like to assign every number a string consisting of digits characters:

alph = 'A':'Z'    
str = [];
for i = 1:digits
    idx = floor(num/26^(digits-i));
    if idx == 0
        idx = 26;
    end
    str = [str alph(idx)];
    num = num - floor(num/26^(digits-i)); %probalby *26^(digits-i); probably ceil(); something is wrong here
    if num == 0
        num = 26^(digits-i-1); 
    end
end

You see, this is already really confusing what I did there, but this is already my 50th attempt, and it still doesn't give me the right characters consistantly. Mostly, whether the strings with 'A' or 'Z' are missing, wrong, or the index is 0 and it throws an error.

1. Is there an easier solution to this?
2. If not included in 1: What did I miss regarding the maths part of this problem?

Thanks a lot for your help!

[Fangjun Jiang]

what version of MATLAB?

[Alexander Keller]

It is 2019b

[Stephen23]

"What did I miss regarding the maths part of this problem?"

Excel column indexing is not really base 26 nor base 27, because the zero digit is completely missing.

A few years ago I reviewed all of the threads on this topic, and found that many answers were not reliable above one or two characters. Many solutions use kludges that limit the number of columns and give no warning if they return wrong values.... ugh! Actually the required general solution WHILE-loop is really not that complex, I showed one version in an answer here:

https://www.mathworks.com/matlabcentral/answers/248797-i-need-to-convert-a-number-into-its-column-name-equivalent

You can find some FEX approaches here, most of them are also buggy:

https://www.mathworks.com/matlabcentral/fileexchange?q=excel+column

It is possible to do this (correctly!) without any loop by figuring out the repdigits in base 26... it only takes about 3-4 lines of code.

EDIT: For recent MATLAB versions (which?), probably the best method was given by Fangjun Jiang here:

import matlab.io.spreadsheet.internal.columnLetter
columnLetter(16384)

ans = 'XFD'

[dpb]

How does one know of such other than by such random postings???

Answer 1

[dpb]

function rnge=xlsAddr(row,col)
% Build Excel cell address from row, column
%
% RNGE=XLSADDR(ROW,COL) will return an Excel cell address 
% formed from the input ROW,COL values.  Either input may be
% string or numeric and will be converted to canonical form

if isnumeric(col)
  if ~isscalar(col),  error('Input Column Not Scalar'), end
  N=26+(col==26);     % kludge to fix up rollover Z-->AA 
  rnge=num2str('A'+[fix(col/N) rem(col,N)]-1,'$%c%c');
  rnge(rnge=='@')=[];   % cleanup for single character
else
  rnge=['$' col];
end
if isnumeric(row)
  if ~isscalar(row),  error('Input Row Not Scalar'), end
  rnge=[rnge num2str(row,'$%d')];
else
  row=num2str(row,'$%d');
  if ~all(ismember(row,'0':'9')),  error('Invalid Excel Address: Row not numeric'), end
  rnge=[rnge row];
end

is my utility function I threw together...it seems to work ok athough I'm sure it could be made much more elegant.

As you can see, I also had some issues with the wraparound that I just kludged...

[Fangjun Jiang]

@dpb, I bet you have older versions of MATLAB. Look inside xlswrite.m for clues to enhance your utility.

function s = dec2base27(d)
% dec2base27    Convert decimal data to base27 data
%
% s=DEC2BASE27(D) returns the representation of D as a string in base 27,
% expressed as 'A'..'Z', 'AA','AB'...'AZ'. Note, there is no zero
% digit, so strictly we have hybrid base26, base27 number system.  D must
% be an integer bigger than 0 and smaller than 2^52. Excel has a maximum
% number of columns as 256 (IV) in a worksheet.
% 
% Example:
% 
%    dec2base27(1) returns 'A'
%    dec2base27(26) returns 'Z'
%    dec2base27(27) returns 'AA'
% 
% See also base27dec
% 
% This function is taken and modified from MATLAB function xlswrite.m

[dpb]

Well, I'm currently on R2019b, but the above was put together at least 22 year ago now, and I've seen no reason to modify it or change. "You can't teach an old dog..." :)

It is interesting comment on the "modified base"; it hadn't dawned on me before about why had the rollover problem is that the "number" system in Excel is missing 0 is the culprit; I just patched it and went on. Consultants don't have time to worry about the "why", much...

[Stephen23]

Note that both TMW's earlier function and dpb's answer are limited to two characters (i.e. fewer than the number of columns of Excel 2007 and later, which go up to column XFD).

[Fangjun Jiang]

I will just use built-in method now.

>> import matlab.io.spreadsheet.internal.columnLetter
>> columnLetter(100000)
ans =
    'EQXD'

[dpb]

" Excel 2007 and later, which go up to column XFD"

I wasn't aware of that, either, not really being an Excel person.

Can't imagine what it would be like; it's bad enough with the workbooks I do have to deal with that go up to roughly 50 columns.

[Alexander Keller]

Thank you for your discussion. Yes you got the point. It is some base 26 or 27 stuff. 26 or 27, because indeed, it seems like the 0 is missing. But not completely missing, since you could still imagine it as "initial" digit, i.e. A = 0A = 1 = 01. However, the columnLetter function works well for me, still it also manages numbers bigger than 702, where the 3 digits Excel-numbers start.

[Stephen23]

"it seems like the 0 is missing. But not completely missing, since you could still imagine it as "initial" digit, i.e. A = 0A = 1 = 01."

No, the zero digit really is missing entirely. How does adding an implicit leading zero make any difference to the skipped index for 27 (i.e. "A0")? Excel jumps straight from Z to AA (and from AZ to BA, etc.). Every zero digit is missing.

Adding infinite implicit leading zeros makes zero(!) difference, is just an irrelevant distraction from the actual problem.

"I wasn't aware of that, either, not really being an Excel person."

I have often processed reasonably large data files, for which knowing the actual limits of Excel became quite important (and ergo, getting the indexing right). These are not just hypothetical concerns either:

https://www.independent.co.uk/news/uk/politics/coronavirus-missing-tests-microsoft-excel-matt-hancock-b859322.html

https://www.theguardian.com/politics/2020/oct/05/how-excel-may-have-caused-loss-of-16000-covid-tests-in-england

Answer 2

[Fangjun Jiang]

1. In old versions of MATLAB, you can find that function inside xlswrite.m. I used to copy that for my own use.
2. In R2019b, it seems it is using "import matlab.io.spreadsheet.internal.columnLetter". You could do the same.
3. dec2base() is the related function. You want base to be 'ABCDEFGHIJKLMNOPQRSTUVWXYZ' which is 26. But when you specify the base to be 26, the function output string of '0123456789ABCDEFGHIJKLMNOP'. You could do some further conversion.

[Fangjun Jiang]

also see the notes in my comment to @dpb's answer.

[Stephen23]

The missing zeros make it more complex than just converting the base.