Excel Column Convert Letters to Numbers

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.