Excel Column Number To Column Name

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

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)

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

[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]

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]

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

[Malcolm Lidierth]

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

[Matt G]

The problems have been fixed.
Thanks,
-Matt

[Kevin J. Delaney]

Perfect! Just what I was looking for.

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