I need to convert a number into its column name equivalent

I'm working on a script that generate some Excel documents and I need to convert a number into its column name equivalent. For example: 1 => A 2 => B 27 => AA 28 => AB 14558 => UMX

[Stephen23]

I was curious about the systematic bugs in the algorithms, which several functions shown on this page demonstrate (producing either errors or incorrect output for some specific letters). So I wrote this simple test function (attached) for checking any such conversion function. It checks all 16384 columns supported by Excel 2007 and later. Note that for simplicity's sake, it does not handle row numbers, so please first modify your function to return the column letters only.

Enjoy!

Answer 1

[Praveen Bulusu]

You can use the following functions. It works for any number.

num=14558 
a=num2xlcol(num) % convert number to xlcol
b=xlcol2num(a)   % convert xlcol to num

function xlcol_addr=num2xlcol(col_num)
% col_num - positive integer greater than zero
    n=1;
    while col_num>26*(26^n-1)/25
        n=n+1;
    end
    base_26=zeros(1,n);
    tmp_var=-1+col_num-26*(26^(n-1)-1)/25;
    for k=1:n
        divisor=26^(n-k);
        remainder=mod(tmp_var,divisor);
        base_26(k)=65+(tmp_var-remainder)/divisor;
        tmp_var=remainder;
    end
    xlcol_addr=char(base_26); % Character vector of xlcol address
end

function xlcol_num=xlcol2num(xlcol_addr)
% xlcol_addr - upper case character
    if ischar(xlcol_addr) && ~any(~isstrprop(xlcol_addr,"upper"))
        xlcol_num=0;
        n=length(xlcol_addr);
        for k=1:n
            xlcol_num=xlcol_num+(double(xlcol_addr(k)-64))*26^(n-k);
        end
    else
        error('not a valid character')
    end
end

[John McDowell]

These are both really useful functions! Thanks for sharing them

[Frederico Pratas]

This is useful, adding some generality to it:

function xlcol_num = xlcol2num(xlcol_addr)
    if ischar(xlcol_addr)
        xlcol_addr = upper(xlcol_addr);        
        xlcol_num=0;
        n=length(xlcol_addr);
        for k=1:n
            xlcol_num = xlcol_num + (double(xlcol_addr(k))-double('A')+1)*(length('A':'Z')^(n-k))
        end
    else
        error('not a valid character')
    end
end

Answer 2

[Stephen23]

Unfortunately Andrei Bobrov's answer does not really take into account the missing zeros, which means that it leads to a kind of "off by one" bug.

Here is an alternative approach which tests correctly on all columns currently supported by Excel (A-XFD) and is limited only by floating point precision (i.e. practically unlimited columns):

d = [1, 2, 26, 27, 28, 702, 703, 704, 14558, 16384]; % A,B,Z,AA,AB,ZZ,AAA,AAB,UMX,XFD
s = arrayfun(@int2xlcol,d)

s = 1×10 string array
    "A"    "B"    "Z"    "AA"    "AB"    "ZZ"    "AAA"    "AAB"    "UMX"    "XFD"

function str = int2xlcol(num)
raw = 'A':'Z';
str = raw(1+rem(num-1,26));
tmp = fix((num-1)/26);
while any(tmp)
    str = [raw(1+rem(tmp-1,26)),str]; %#ok<AGROW>
    tmp = fix((tmp-1)/26);
end
str = string(str);
end

You can also find several submissions on FEX which claim to make this conversion, e.g.:

https://www.mathworks.com/matlabcentral/fileexchange?q=Excel+A1

[Stephen23]

The same approach can also convert multiple values at once:

d = [1, 2, 26, 27, 28, 702, 703, 704, 14558, 16384]; % A,B,Z,AA,AB,ZZ,AAA,AAB,UMX,XFD
z = reshape('A':'Z',[],1);
c = num2cell(z(1+rem(d-1,26)));
v = fix((d-1)/26);
while any(v)
    x = v>0;
    c(x) = strcat(z(1+rem(v(x)-1,26)),c(x));
    v = fix((v-1)/26);
end
s = string(c)

s = 10×1 string array
    "A"
    "B"
    "Z"
    "AA"
    "AB"
    "ZZ"
    "AAA"
    "AAB"
    "UMX"
    "XFD"

Answer 3

[Andrei Bobrov]

z = 'A':'Z';

d = [1, 2, 27, 28, 14558];

ll = ceil(log(max(d(:)))/log(26));
bs = rem(floor(d(:)*26.^(1-ll:0)),26);

out = cellfun(@(x)z(x(x > 0)),num2cell(bs,2),'un',0);

or

out = arrayfun(@(x)z(rem(floor(x*26.^(1-floor(log(x)/log(26)+1):0)),26)),d(:),'un',0);

[Praveen Choudhury]

Undefined function or variable 'b26'.

Error in cal_comp (line 3)

ll = ceil(log(max(d(:)))/log(b26));

[Andrei Bobrov]

corrected :)

[Daniel LeBrun]

Hey I was using this code and noticed that when it gets to "Z" it instead writes "A". Have to find a way to have the bs term not forget the 26th.

[Stephen23]

Buggy code! It does not correctly parse outputs which should contain 'Z':

>> d = [26,51,52,53,677,728]; % -> {'Z','AY','AZ','BA','ZA','AAZ'}
>> z = 'A':'Z';
>> ll = ceil(log(max(d(:)))/log(26));
>> bs = rem(floor(d(:)*26.^(1-ll:0)),26);
>> out = cellfun(@(x)z(x(x > 0)),num2cell(bs,2),'un',0)
out = 
    'A'
    'AY'
    'B'
    'BA'
    'AA'
    'AB'
>> out = arrayfun(@(x)z(rem(floor(x*26.^(1-floor(log(x)/log(26)+1):0)),26)),d(:),'un',0)
Subscript indices must either be real positive integers or logicals.
Error in @(x)z(rem(floor(x*26.^(1-floor(log(x)/log(26)+1):0)),26))

[Andrei Bobrov]

Yes! (Thanx Daniel and Stephen!)

Other variant:

z = 'A':'Z';
d = [1, 2, 26, 27, 28, 702, 703, 704, 14558, 16384,18278];
ll = ceil(log(max(d(:)))/log(26));
bs = rem(floor(d(:)*26.^(1-ll:0)),26);
lo = bs(:,end) == 0;
bs(lo,:) = circshift(bs(lo,:),-1,2)*26;
out = cellfun(@(x)z(x(x > 0)),num2cell(bs,2),'un',0);

[Xiaobing Zhou]

Andrei Bobrov, works for 26, but not for 2*26, 3*26, ..., n*26

[Andrei Bobrov]

function ch = Int2lat(d)
    function ch = finner(d)
        ll = floor(log(max(d(:)))/log(26))+1;
        out = rem(floor(d(:)*26.^(1-ll:0)),26);
        lo = ~out;
        while any(lo)
            out = out + lo*26 - circshift(lo,-1);
            out = out(cumsum(out,2) > 0);
            lo = ~out;
        end
        abc = 'A':'Z';
        ch = abc(out);
    end
    
    ch = arrayfun(@finner,d,'un',0);
end

>> h = Int2lat((1:20)'*26)
h =
  20×1 cell array
    {'Z' }
    {'AZ'}
    {'BZ'}
    {'CZ'}
    {'DZ'}
    {'EZ'}
    {'FZ'}
    {'GZ'}
    {'HZ'}
    {'IZ'}
    {'JZ'}
    {'KZ'}
    {'LZ'}
    {'MZ'}
    {'NZ'}
    {'OZ'}
    {'PZ'}
    {'QZ'}
    {'RZ'}
    {'SZ'}
>> 

[Stephen23]

@Andrei Bobrov: note that function FINNER() returns incorrect 'Z' outputs on MATLAB versions R2016a and earlier, due to the change in the default behavior of CIRCSHIFT's 2nd input argument. For example:

>> finner(26)
ans =
AY

Answer 4

[Remco Hamoen]

This function might belp. It converts row and column to 'A1' combinations:

function CELL = xlRC2A1(ROW,COL)
%% Returns the column characters of Excel given a certain column number
% Input COL : number of column
% Output CHAR : Character combination in Excel

    if COL <= 26                        % [A..Z]
        CHAR = char(mod(COL-1,26)+1+64);
        CELL = [CHAR num2str(ROW)];
    elseif COL <= 702                   % [AA..ZZ]
        COL = COL-26;    
        CHAR1 = char(floor((COL-1)/26)+1+64);
        CHAR0 = char(mod(COL-1,26)+1+64);
        CHAR = [CHAR1 CHAR0];
        CELL = [CHAR num2str(ROW)];
    elseif COL <= 16384                 % [AAA..XFD]
        COL = COL-702; 
        CHAR2 = char(floor((COL-1)/676)+1+64);
        COL=COL-(floor((COL-1)/676))*676;
        CHAR1 = char(floor((COL-1)/26)+1+64);
        CHAR0 = char(mod(COL-1,26)+1+64);
        CHAR = [CHAR2 CHAR1 CHAR0];
        CELL = [CHAR num2str(ROW)];
    else
        disp('Column does not exist in Excel!');
    end
end

Regards,

Remco

Answer 5

[Xavier]

Very good question ...

This should work whatever what ...

Xavier

Answer 6

[Daniel Pereira]

These two functions have been validated with every excel column names form A (1) to XFD (16348).

Maybe some cheks for integer input is missing, but if you use it responsibly, you will find no problems.

You can call them inside arrayfun or cellfun respectively:

% Calls for arrays and cells
ColumnNumbers  = 1:16348;
ColumnNames    = arrayfun(@num2col, ColumnNumbers, 'un',0);
ColumnNumbers2 = cellfun(@col2num, ColumnNames);

% Tests
assert(isequal(ColumnNumbers2,ColumnNumbers)); % Check that col2num(num2col(x)) == x for every x

tic;
assert(isequal(num2col(7),'G'));
assert(isequal(num2col(21),'U'));
assert(isequal(num2col(56),'BD'));
assert(isequal(num2col(256),'IV'));
assert(isequal(num2col(539),'TS'));
assert(isequal(num2col(1000),'ALL'));
assert(isequal(num2col(16384),'XFD'));
toc;

Elapsed time is 0.016305 seconds.

% Fucntions
function col = num2col(num)
    col = '';
    if num>0
        col = [num2col(floor((num-1)/26)) char('A'+mod(num-1,26))];            
    end
end

function num = col2num(col)
    num = sum((upper(col)-'A'+1).*(26.^(length(col)-1:-1:0)));
end