I need to convert a number into its column name equivalent

155 views (last 30 days)
Praveen Choudhury on 15 Oct 2015
Commented: Frederico Pratas on 1 Apr 2021 at 12:00
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
Remco Hamoen on 19 Apr 2020
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

Praveen Bulusu on 22 Feb 2020
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
% 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
end
% xlcol_addr - upper case character
xlcol_num=0;
for k=1:n
end
else
error('not a valid character')
end
end
Frederico Pratas on 1 Apr 2021 at 12:00
This is useful, adding some generality to it:
xlcol_num=0;
for k=1:n
end
else
error('not a valid character')
end
end

Stephen Cobeldick on 10 Dec 2019
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:
>> 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);
>> s = z(1+rem(d-1,26));
>> v = fix((d-1)/26);
>> while any(v), s(v>0,end+1) = z(1+rem(v(v>0)-1,26)); v = fix((v-1)/26); end
>> s = fliplr(s)
s =
A
B
Z
AA
AB
ZZ
AAA
AAB
UMX
XFD
Or with a cell array of char vectors (could easily be adapted to strings):
>> 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), c(v>0) = strcat(z(1+rem(v(v>0)-1,26)),c(v>0)); v = fix((v-1)/26); end
>> c
c =
'A'
'B'
'Z'
'AA'
'AB'
'ZZ'
'AAA'
'AAB'
'UMX'
'XFD'
You can also find several submissions on FEX which claim to make this conversion, e.g.:

Andrei Bobrov on 15 Oct 2015
Edited: Andrei Bobrov on 16 Oct 2015
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);
Andrei Bobrov on 22 Nov 2020
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'}
>>

Daniel LeBrun on 10 Dec 2019
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.