# Convert given excel column name to column Index, ex ‘A=0’, ‘AA=26’

Other day I needed to convert excel spreadsheet column name to column index when having 200+ columns is easier to express as ‘FB’ instead of 157

```A = 0
B = 1
AA = 26
AA = 27
FB = 157
```

This code will work for any number of column names.

``` /** * Convert given excel column name to column Index, ex 'A=0', 'AA=26' * @param columnName * @return 0 based index of the column */ private static short convert2ColumnIndex(String columnName) { columnName = columnName.toUpperCase(); short value = 0; for (int i = 0, k = columnName.length() - 1; i < columnName.length(); i++, k--) { int alpabetIndex = ((short) columnName.charAt(i)) - 64; int delta = 0; // last column simply add it if (k == 0) { delta = alpabetIndex - 1; } else { // aggregate if (alpabetIndex == 0) delta = (26 * k); else delta = (alpabetIndex * 26 * k); } value += delta; } return value; }```

# Converting from index to column name

This process is trivial we simply keep on taking mod 26 from index till we have nothing left, and converting that value to char.

### 5 thoughts on “Convert given excel column name to column Index, ex ‘A=0’, ‘AA=26’”

1. Actually your code is incorrect, I believe it at most works for length 2.
It is as simple as converting from index to column name.
See the modified version below.

public static int convert2ColumnIndex(String columnName) {
columnName = columnName.toUpperCase();
int value = 0;
for (int i = 0; i < columnName.length(); i++) {
int delta = ( columnName.charAt(i)) – 64;
value = value*26+ delta;
}
return value-1;
}

2. int columnIndex = 0;
Regex regex = new Regex(“[A-Za-z]”);
foreach (char eachCharaterIndex in regex.Match(columnNameIndex).Value.ToLower().ToCharArray())
{
columnIndex = (eachCharaterIndex % 32) + columnIndex;
}
return columnIndex;

3. private int ExcelColumnIndex(string columnNameIndex)
{
int columnIndex = 0;
Regex regex = new Regex(“[A-Za-z]”);
foreach (char eachCharaterIndex in regex.Match(columnNameIndex).Value.ToLower().ToCharArray())
{
columnIndex = (eachCharaterIndex % 32) + columnIndex;
}
return columnIndex;
}