Convert a number to an Excel column heading

As part of a project I’ve been working on in the office, I had a need to programmatically work with Microsoft Excel. This is a fairly easy process with the Interop.Excel DLL, however, sometimes there can be a need to work with language data types and convert them in a way that can be used by Excel. An example of this might be programmatically attempting to obtain a range of cells, without knowing the full extent of the data.

For example, if you’re trying to create a chart programmatically, the data you wish to use in the chart may have a varying length at run time. This means you need to calculate the chart range at run time, rather than using a fixed range which may miss data. As long as you know how many columns of data you have (numerically), you can convert this to an Excel heading with the script provided here.

Excel column headings

Excel column headings are the labels above each column which follow the alphabet, A through to Z. When you reach the 27th column, it becomes AA through to ZZ, and so on and so forth. But if you only have the number of columns (e.g. you have 475 columns of data), it might be tricky working out where in the AA/AAA column space this is. Hopefully this script will be able to help you.

Script to convert int to Excel column heading

The following script has been written in the C# language (as that’s what I needed it for) but the principle is basic enough to put into any language. All you need to really change is how the conversion to the ‘char’ data type is handled – in this example it uses the Convert class, obviously in Java or C++ you’ll need to use their equivalent.

private String ConvertToColumnName(int number)
{
    int mod = 0;
    String columnHeading = "";

    while (number > 0)
    {
        mod = (number - 1) % 26;
        columnHeading = Convert.ToChar(65 + mod).ToString() + columnHeading;
        number = (int)((number - mod) / 26);
    }

    return columnHeading;
}

For the number 475, this script would return the string “RG”.

Hopefully this script will help you convert any number you need into a column heading for use with Excel. One thing to note is that this assumes your column A starts at 1. If, for some reason, column A starts at 0, you’ll need to amend the column number coming into the function by one (e.g. number += 1 or similar before the “int mod” line).