I need to convert a number into its column name equivalent

56 次查看（过去 30 天）
Praveen Choudhury
Praveen Choudhury 2015-10-15
移动：Stephen23 2025-2-25
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 个评论
Stephen23
Stephen23 2022-5-18
编辑：Stephen23 2023-10-11
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!

请先登录，再进行评论。

请先登录，再回答此问题。

回答（5 个）

Praveen Bulusu
Praveen Bulusu 2020-2-22
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 个评论
John McDowell
John McDowell 2020-7-6
编辑：John McDowell 2020-7-6
These are both really useful functions! Thanks for sharing them
Frederico Pratas
Frederico Pratas 2021-4-1
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

请先登录，再进行评论。

Stephen23
Stephen23 2019-12-10
编辑：Stephen23 2022-6-15
Ran in:
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
  1 个评论
Stephen23
Stephen23 2022-6-15
编辑：Stephen23 2022-6-15
Ran in:
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"

请先登录，再进行评论。

Andrei Bobrov
Andrei Bobrov 2015-10-15
编辑：Andrei Bobrov 2015-10-16
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);
  8 个评论
显示 6更早的评论
Andrei Bobrov
Andrei Bobrov 2020-11-22
编辑：Andrei Bobrov 2020-11-22
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
Stephen23 2022-5-18
编辑：Stephen23 2022-6-15
@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

请先登录，再进行评论。

Remco Hamoen
Remco Hamoen 2020-4-19
移动：Stephen23 2025-2-25
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
Xavier
Xavier 2025-2-25
移动：Stephen23 2025-2-25
Very good question ...
This should work whatever what ...
Xavier

类别

在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息

标签

Community Treasure Hunt

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

Start Hunting!

Translated by