CFLib.org – Common Function Library Project

excelColumnNameToColumnNumber(columnNamePassIn)

Last updated August 22, 2011

author

Nolan Erck

Version: 1 | Requires: CF5 | Library: UtilityLib

Description:
Converts an Excel Column Name (AB) to its numeric column position (i.e. 28). Assumes column numbers are indexed starting from 1 (1=A,2=B,...)

Return Values:
Returns a number.

Example:

excelColumnNametoColumnNumber( "AB" );

excelColumnNametoColumnNumber( "CC" );

excelColumnNametoColumnNumber( "F" );

Parameters:

Name Description Required
columnNamePassIn Column name (as string) to convert. Yes

Full UDF Source:

<!---
 Converts an Excel Column Name to its numeric column position.
 
 @param columnNamePassIn      Column name (as string) to convert. (Required)
 @return Returns a number. 
 @author Nolan Erck (nolan.erck@gmail.com) 
 @version 1, August 22, 2011 
--->
function excelColumnNameToColumnNumber( columnNamePassedIn ) {
    var columnName = UCase( Trim( arguments.columnNamePassedIn ) ); // clean up our data a bit to make some ASCII math easier...
    var colLength  = Len( Trim( columnName ) );
    var cur_Char   = "";
    var index      = colLength;
    var columnNumber = 0;
    var expBase    = 26;
    var digitPlaceHolder = 0;
    var subTotal   = 0;

    while( index gt 0 )
    {
        cur_Char     = Mid( columnName, index, 1 );
        columnNumber = ( ( Asc( cur_Char ) - 64 ) * ( expBase ^ digitPlaceHolder ) );
        subTotal    += columnNumber;

        index--;        
        digitPlaceHolder++;
    }

    return subTotal;
}

Search CFLib.org


Latest Additions

Raymond Camden added
QueryDeleteRows
November 04, 2017

Leigh added
nullPad
May 11, 2016

Raymond Camden added
stripHTML
May 10, 2016

Kevin Cotton added
date2ExcelDate
May 05, 2016

Raymond Camden added
CapFirst
April 25, 2016

Created by Raymond Camden / Design by Justin Johnson