CFLib.org – excelColumnNameToColumnNumber

excelColumnNameToColumnNumber(columnNamePassIn)

Last updated August 22, 2011

author

Version: 1 | Requires: ColdFusion 5 | Library: UtilityLib

Rated 0 time(s). Average Rating: 0

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:

view plain print about

excelColumnNametoColumnNumber( "AB" );

excelColumnNametoColumnNumber( "CC" );

excelColumnNametoColumnNumber( "F" );

excelColumnNametoColumnNumber( "AB" );

excelColumnNametoColumnNumber( "CC" );

excelColumnNametoColumnNumber( "F" );

Parameters:

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

Full UDF Source:

	
	
			
			
			
				
				view plain
				
				
				copy to clipboard
				
				
				
				
				print
				
				
				about
				ColdFISH is developed by Jason Delmore.  Source code and license information available at coldfish.riaforge.org.  Version 3.1.1
				
			<!---
 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 (&#110;&#111;&#108;&#97;&#110;&#46;&#101;&#114;&#99;&#107;&#64;&#103;&#109;&#97;&#105;&#108;&#46;&#99;&#111;&#109;) 
 @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;
}
		<!---
 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;
}