algorithmexcelmatlab

Convert Excel Column Number to Column Name in Matlab


I am using Excel 2007 which supports Columns upto 16,384 Columns. I would like to obtain the Column name corresponding Column Number.

Currently, I am using the following code. However this code supports upto 256 Columns. Any idea how to obtain Column Name if the column number is greater than 256.

function loc = xlcolumn(column)

    if isnumeric(column)
        if column>256
            error('Excel is limited to 256 columns! Enter an integer number <256');
        end
        letters = {'A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z'};
        count = 0;
        if column-26<=0
            loc = char(letters(column));
        else
            while column-26>0
                count = count + 1;
                column = column - 26;
            end
            loc = [char(letters(count)) char(letters(column))];
        end

    else
        letters = ['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z'];
        if size(column,2)==1
            loc =findstr(column,letters);
        elseif size(column,2)==2
            loc1 =findstr(column(1),letters);
            loc2 =findstr(column(2),letters);
            loc = (26 + 26*loc1)-(26-loc2);
        end
    end

Thanks


Solution

  • As a diversion, here is an all function handle example, with (almost) no file-based functions required. This is based on the dec2base function, since Excel column names are (almost) base 26 numbers, with the frustrating difference that there are no "0" characters.

    Note: this is probably a terrible idea overall, but it works. Better solutions are probably found elsewhere in the file exchange.

    First, the one file based function that I couldn't get around, to perform arbitrary depth function composition.

    function result = compose( fnHandles )
    %COMPOSE Compose a set of functions
    %    COMPOSE({fnHandles}) returns a function handle consisting of the
    %    composition of the cell array of input function handles.  
    %
    %    For example, if F, G, and H are function handles with one input and
    %    one output, then: 
    %        FNCOMPOSED = COMPOSE({F,G,H});
    %        y = FNCOMPOSED(x);
    %    is equivalent to 
    %        y = F(G(H(x)));
    if isempty(fnHandles)
        result = @(x)x;
    elseif length(fnHandles)==1
        result = fnHandles{1};
    else
        fnOuter     = fnHandles{1};
        fnRemainder = compose(fnHandles(2:end));
        result = @(x)fnOuter(fnRemainder(x));
    end
    

    Then, the bizarre, contrived path to convert base26 values into the correct string

    %Functions leading to "getNumeric", which creates a numeric, base26 array
    remapUpper = @(rawBase)(rawBase + (rawBase>='A')*(-55));          %Map the letters 'A-P' to  [10:26]
    reMapLower = @(rawBase)(rawBase + (rawBase<'A')*(-48));          %Map characters  '0123456789' to [0:9]
    getRawBase = @(x)dec2base(x, 26);
    
    getNumeric = @(x)remapUpper(reMapLower(getRawBase(x)));
    
    %Functions leading to "correctNumeric"
    %    This replaces zeros with 26, and reduces the high values entry by 1.
    %    Similar to "borrowing" as we learned in longhand subtraction
    borrowDownFrom   = @(x, fromIndex) [x(1:(fromIndex-1))  (x(fromIndex)-1) (x(fromIndex+1)+26)  (x((fromIndex+2):end))];
    borrowToIfNeeded = @(x, toIndex)   (x(toIndex)<=0)*borrowDownFrom(x,toIndex-1) + (x(toIndex)>0)*(x);  %Ugly numeric switch
    
    getAllConditionalBorrowFunctions = @(numeric)arrayfun(@(index)@(numeric)borrowToIfNeeded(numeric, index),(2:length(numeric)),'uniformoutput',false);
    getComposedBorrowFunction = @(x)compose(getAllConditionalBorrowFunctions(x));
    
    correctNumeric = @(x)feval(getComposedBorrowFunction(x),x);
    
    %Function to replace numerics with letters, and remove leading '@' (leading
    %zeros)
    numeric2alpha = @(x)regexprep(char(x+'A'-1),'^@','');
    
    %Compose complete function
    num2ExcelName = @(x)arrayfun(@(x)numeric2alpha(correctNumeric(getNumeric(x))), x, 'uniformoutput',false)';
    

    Now test using some stressing transitions:

    >> num2ExcelName([1:5 23:28 700:704 727:729 1024:1026 1351:1355 16382:16384])
    ans = 
    'A'
    'B'
    'C'
    'D'
    'E'
    'W'
    'X'
    'Y'
    'Z'
    'AA'
    'AB'
    'ZX'
    'ZY'
    'ZZ'
    'AAA'
    'AAB'
    'AAY'
    'AAZ'
    'ABA'
    'AMJ'
    'AMK'
    'AML'
    'AYY'
    'AYZ'
    'AZA'
    'AZB'
    'AZC'
    'XFB'
    'XFC'
    'XFD'