Title: Convert between Excel-style column numbers and names in C#

Excel uses both numbers and names to identify columns. A = 1, B = 2, ..., Z = 26, AZ = 27, ..., XFD = 16,384. This example converts between these Excel-style column numbers and names. It's a little trickier than you'd think.

When you type a column name, the program converts it into a number and then converts that number back into a name so you can see that the two conversions work.

The following ColumnNameToNumber function converts a column name (as in AZ) into a number (52).

// Return the column number for this column name. private int ColumnNameToNumber(string col_name) { int result = 0; // Process each letter. for (int i = 0; i < col_name.Length; i++) { result *= 26; char letter = col_name[i]; // See if it's out of bounds. if (letter < 'A') letter = 'A'; if (letter > 'Z') letter = 'Z'; // Add in the value of this letter. result += (int)letter - (int)'A' + 1; } return result; }

The function loops through the letters in the column name. For each letter, it multiplies the previous result number by 26 to move any earlier digits to the left to make room for the new one. It then gets the next letter and makes sure it's between A and Z.

The code then subtracts the ASCII value of A from the letter's ASCII value. For example, if the letter is A, this gives ASCII(A) - ASCII(A) = 0. Because A represents 1 in Excel column numbering, the code then adds 1.

The code continues the loop until it has processed each letter.

I found the opposite conversion more confusing. Function ColumnNumberToName handles the conversion from column number (as in 52) to name (AZ).

// Return the column name for this column number. private string ColumnNumberToName(int col_num) { // See if it's out of bounds. if (col_num < 1) return "A"; // Calculate the letters. string result = ""; while (col_num > 0) { // Get the least significant digit. col_num -= 1; int digit = col_num % 26; // Convert the digit into a letter. result = (char)((int)'A' + digit) + result; col_num = (int)(col_num / 26); } return result; }

As long as the column number is greater than zero, the code loops.

Inside the loop, the program subtracts 1. This is the part I found most confusing. If you look at the previous function, you'll see that the code adds 1 to each digit to make A represent 1 not 0. Here we are subtracting it back out so A = 0, B = 1, and so forth up to Z = 25.

Next the code calculates the column number mod 26. That pulls out the last letter's value: A = 0, etc. The code turns that into a letter and adds it to the result string.

The code then divides the column number by 26, throwing away any remainder, to remove this letter from the remaining number.

The code repeats until it has pulled all of the letters out of the column number.

Download the example to experiment with it and to see additional details.