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

조회 수: 58(최근 30일)
Praveen Choudhury 15 Oct 2015
Edited: Andrei Bobrov 12 Dec 2019
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

로그인 to comment.

### 답변 수 (3)

Andrei Bobrov 15 Oct 2015
Andrei Bobrov 님이 편집함. 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);

#### 댓글 수: 4

표시 이전 댓글 수: 1
Andrei Bobrov 16 Oct 2015
corrected :)
Stephen Cobeldick 10 Dec 2019
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 12 Dec 2019
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 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.

#### 댓글 수: 0

로그인 to comment.

Stephen Cobeldick 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.:

#### 댓글 수: 0

로그인 to comment.

이 질문에 답변하려면 로그인을(를) 수행하십시오.

Translated by