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;
}
blog comments powered by Disqus

Search CFLib.org


Latest Additions

Kevin Cotton added
date2ExcelDate
May 5, 2016

Raymond Camden added
CapFirst
April 25, 2016

Chris Wigginton added
loremIpsum
January 18, 2016

Gary Stanton added
calculateArrival...
November 19, 2015

Sebastiaan Naafs - van Dijk added
getDaysInQuarter
November 13, 2015

Created by Raymond Camden / Design by Justin Johnson