Code covered by the BSD License  

Highlights from
Excel Column Number To Column Name

5.0

5.0 | 1 rating Rate this file 32 Downloads (last 30 days) File Size: 1.76 KB File ID: #15748
image thumbnail

Excel Column Number To Column Name

by

 

30 Jul 2007 (Updated )

Converts an array of numbers to a cell array of Excel column names

| Watch this File

File Information
Description

XLSCOLNUM2STR takes in an array of numbers and returns a cellular array of the same size with cell of corresponding Excel column names.

example:
n=[1 10;
   53 256]
c=xlsColNum2Str(n);
c={'A' , 'J';
   'BA', 'IV'}

See Also XLSCOLSTR2NUM

MATLAB release MATLAB 7.3 (R2006b)
Tags for This File   Please login to tag files.
Please login to add a comment or rating.
Comments and Ratings (8)
09 Jul 2010 Ly Tran

Hi, I have the same problem as D S:
xlsColNum2Str(30)
??? Too many inputs.

Error in ==> xlsColNum2Str at 36
colChar(colNum>=L & colNum<=U) = ...
I am using Matlab 7.0.4.365. Can that be a problem?
Thank you.

30 Jul 2009 Kevin J. Delaney

Perfect! Just what I was looking for.

12 Apr 2009 Matt G

The problems have been fixed.
Thanks,
-Matt

14 Oct 2008 Malcolm Lidierth

Some problems:
>> xlsColNum2Str(53)
ans =
'BA'
>> xlsColNum2Str(1353)
ans =
'BA'
>> length(unique(xlsColNum2Str(1:16384)))
ans =
15786
not 16384.

03 Aug 2007 Matt G

Hi D.S,

I am not sure why you are getting the error. I am not at work today but when I get in I will run the function on some different MATLAB versions. To see if I can recreate the error.

-Matt

02 Aug 2007 D S

Matt- Thanks for finding that error in 'num2abc'. I updated the logic so it should be right now.

I downloaded your function and tried running the example, but I get:

??? Too many inputs.
Error in ==> xlsColNum2Str at 36
colChar(colNum>=L & colNum<=U)=...

02 Aug 2007 Matt G

D S,

Thanks for the comment. I took a look at NUM2ABC but it seems that there are some holes. If you run the following script:
for n=1:256
try
a{n}=num2abc(n);
catch
n
end
end

you will find that 52, 78, 104, 130, 156, 182, 208, 234 (multiples of 26) return errors.

Also as I noted in the m-file Excel 2007 has 16,384 columns. So your code would need to modified if for some reason someone needs more than 256 columns.

As for as converting from a character array to number I didn’t post my function but I will now. It is simple…

function [ colNum ] = xlsColStr2Num( colChar )

colNum=cellfun(@(x) (sum((double(x)-64).*26.^(length(x)-1:-1:0))),colChar);

02 Aug 2007 D S

Just thought I'd mention NUM2ABC (also posted on the FEX) was made for the same task. There is also an inverse function, ABC2NUM. These functions work on a single value and return a string or an integer, respectively. These are in the "Utilities > Data Import/Export" category. I'm not sure, but that might be a better category for this submission as well.

Updates
01 Aug 2007

Updated the summary to fit the 100 char limit

01 Oct 2007

Revised title

12 Apr 2009

Fixed bug noted by Malcolm Lidierth.

03 Aug 2009

Updated summary

Contact us