File Exchange

image thumbnail

Excel Column Number To Column Name

version 1.4.0.0 (1.76 KB) by Matt G
Converts an array of numbers to a cell array of Excel column names

18 Downloads

Updated 03 Aug 2009

View Version History

View License

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

Cite As

Matt G (2021). Excel Column Number To Column Name (https://www.mathworks.com/matlabcentral/fileexchange/15748-excel-column-number-to-column-name), MATLAB Central File Exchange. Retrieved .

Comments and Ratings (12)

Szymon

Works perfectly, thanks!

Uma Mahe

This function doesn't work if I call from a program by passing single value argument

Karim Tarbali

thank us so much.

Vladimir

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.

Kevin J. Delaney

Perfect! Just what I was looking for.

Matt G

The problems have been fixed.
Thanks,
-Matt

Malcolm Lidierth

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

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

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)=...

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

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.

MATLAB Release Compatibility
Created with R2006b
Compatible with any release
Platform Compatibility
Windows macOS Linux

Community Treasure Hunt

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

Start Hunting!