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