File Exchange

image thumbnail

Excel Column Number To Column Name

version 1.4 (1.76 KB) by

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

17 Downloads

Updated

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

Comments and Ratings (9)

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

Matt G (view profile)

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.

Updates

1.4

Updated summary

1.3

Fixed bug noted by Malcolm Lidierth.

Revised title

Updated the summary to fit the 100 char limit

MATLAB Release
MATLAB 7.3 (R2006b)

Download apps, toolboxes, and other File Exchange content using Add-On Explorer in MATLAB.

» Watch video