Excel Column Convert Letters to Numbers

Like this blog? Consider exploring one of our sponsored banner ads...

Standard spreadsheet applications use letters to reference columns and numbers to reference rows. I am creating an interface to map columns to data and needed to convert the columns to integers. Column AB for example is really position 29. When you upload a CSV file and spin through it for example, you need the numerical position.

here is a simple function to convert the columns to integers.

function columnNumber($col){
 
	$col = str_pad($col,2,'0',STR_PAD_LEFT);
	$i = ($col{0} == '0') ? 0 : (ord($col{0}) - 64) * 26;
	$i += ord($col{1}) - 64;
 
	return $i;
 
}

And to output all the 2 letter combinations in order, for example to populate a dropdown menu with the choices, you could use a simple nested loop like this.

$out1 = $out2 = '';
 
for($i=65; $i<=90; $i++){
 
	$out1 .= chr($i) . "\n";
 
	for($j=65; $j<=90; $j++){
 
		$out2 .= chr($i) . chr($j) . "\n";
 
	}
 
}
 
echo $out1 . $out2;

If you are still using Microsoft Excel, consider switching to OpenOffice. It is a FREE opensource alternative to the Micorsoft Office suite. Don’t worry, you can still view, edit and create the native Microsoft formats so sharing files with your friends and co-workers is a seamless process. I have used OpenOffice exclusively for years.