MATLAB Answers

I need to convert a number into its column name equivalent

155 views (last 30 days)
Praveen Choudhury
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
  1 Comment
Remco Hamoen
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

Sign in to comment.

Answers (4)

Praveen  Bulusu
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
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
  2 Comments
Frederico Pratas
Frederico Pratas on 1 Apr 2021 at 12:00
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

Sign in to comment.


Stephen Cobeldick
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
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);
  6 Comments
Andrei Bobrov
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'}
>>

Sign in to comment.


Daniel LeBrun
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.

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!