MATLAB Answers

I need to convert a number into its column name equivalent

35 views (last 30 days)
Praveen Choudhury
Praveen Choudhury on 15 Oct 2015
Edited: Andrei Bobrov on 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

  0 Comments

Sign in to comment.

Answers (3)

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

  4 Comments

Show 1 older comment
Stephen Cobeldick
Stephen Cobeldick on 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
Andrei Bobrov on 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);

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.

  0 Comments

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

  0 Comments

Sign in to comment.

Sign in to answer this question.