# I need to convert a number into its column name equivalent

48 ビュー (過去 30 日間)
Praveen Choudhury 2015 年 10 月 15 日
Edited: Andrei Bobrov 2019 年 12 月 12 日
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

#### 0 件のコメント

サインイン to comment.

### 件の回答 (3)

Andrei Bobrov 2015 年 10 月 15 日

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);

#### 4 件のコメント

Andrei Bobrov 2015 年 10 月 16 日
corrected :)
Stephen Cobeldick 2019 年 12 月 10 日
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 2019 年 12 月 12 日
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);

サインイン to comment.

Daniel LeBrun 2019 年 12 月 10 日
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.

#### 0 件のコメント

サインイン to comment.

Stephen Cobeldick 2019 年 12 月 10 日
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.:

#### 0 件のコメント

サインイン to comment.

サインイン してこの質問に回答します。

Translated by