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

[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

[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 :)

[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

[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.

Answer 5

[Qiang Fu]

I had the same requirement shortly before when I used the "writematrix" function in order to convert numerical index to the corresponding column in excel file in MATLAB, and I wrote the following code based on regarding it as a geometric progression, of which the first term and common ratio were both 26.

function ac_id = idx_xlsx(num_col)
    arguments
		num_col {mustBeInteger}
    end
	
	% Create the geometric progression
	n = 0;
	f1 = @(a,q,n) a*(1-q.^n)/(1-q);
	
	% The length of expected result
	while true
		if (num_col > f1(26,26,n)) && (num_col <= f1(26,26,n+1))
			break
		else
			n = n + 1;
		end
	end
	len_id = n + 1;
	
	ac_id = char();
	formalid = f1(26,26,n); % The sum of previous intervals
	curr = num_col - formalid;	% The extra length in current interval
	
	% The 1st character from the left
	if curr/26.^(len_id-1) == fix(curr/26.^(len_id-1))
		ac_id(1) = char(64+fix(curr/26.^(len_id-1)));
	else
		ac_id(1) = char(64+fix(curr/26.^(len_id-1)+1));
	end
	
	% From the 2nd character to the last but one character 
	for j=2:len_id-1
        % Calculate the number of secondary interval included
        % in the extra length
        nomin = curr-power(26,len_id-j+1)*fix(curr/power(26,len_id-j+1));
        % Calculate the number of sub-interval of the secondary interval 
        % included in the "remaining" range
        denomin = power(26,len_id-j);
        idk = nomin / denomin;
        
		if idk == 0
			ac_id(j) = char(64+26);
		elseif (idk == fix(idk)) && (idk ~= 0)
			idk = fix(idk);
			ac_id(j) = char(64+idk);
		else
			idk = fix(idk)+1;
			ac_id(j) = char(64+idk);
		end
	end
	
	% The last character
	if mod(curr,26) ~= 0
		ac_id(len_id) = char(64+mod(curr,26));
	else
		ac_id(len_id) = char(64+26);
	end

end

The output is the following

>> a = idx_xlsx(1368)   % Random number
a = 'AZP'
>> b = idx_xlsx(3250)   % 26*n (n = 1,2,...)
b = 'DTZ'
>> c = idx_xlsx(16384)  % The maximum column of excel file
c = 'XFD'

I' ve tested it with a series of numbers, and I hope this code could be reliable and we could optimize it.