CFLib.org – Common Function Library Project

excelColumnNameToColumnNumber(columnNamePassIn)

Last updated August 22, 2011

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;
}``````

date2ExcelDate
May 5, 2016

CapFirst
April 25, 2016