Converting Between Integers and Spreadsheet Column Labels

Introduction

Anyone who has used a spreadsheet program has probably noticed the system used for numbering columns. Instead of digits, letters are used. The sequence looks like "A" ... "Z", "AA" ... "AZ", "BA" ... "BZ", and so on.

So I thought I nice little project would be to write a class that converts between integers and these column labels.

My SSColumns Class

Listing 1 shows my SSColumns class. This is a static class. All methods can be accessed by calling them directly. (It is not necessary to create an instance of the class.)

This class contains three static methods. ToString() will convert an integer value to a column label.

The Parse() method will convert a column label back to an integer. This method is smart enough to handle both upper and lower case input. It can also handle any leading and trailing whitespace. This method is actually just a wrapper for the TryParse() method. You can call whichever version suits you best.

As you can see from the code, this is not a complicated task. Not much code is require to implement all three methods. However, the logic is slightly askew when compared to logic commonly used to convert integers to strings. Looking at the ToString() method, you can see the value needs to be fudged each time through the loop (except for the first time). The method subtracts one from the value each time through the loop except the first time.

This bit of twisted logic is required because of the way multiple letters are used. When converting a value to a decimal string, the first value with two digits is "10". Note that "1" is the second digit ("0" is the first). However, in the case of column labels, the first value with two digits is "AA". Note that "A" is the very first digit. This seemed a little strange, and the reverse must be done in the parsing methods.

I should point out that these methods intepret the integer values as being 0-based. That is, the first column is column 0. If you need your columns to be 1-based, simply decrement the value before calling ToString().

Listing 1: The SSColumns Class

/// <summary>
/// Class for converting between integers and spreadsheet column letters.
/// </summary>
static class SSColumns
{
    private const string _letters = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";

    /// <summary>
    /// Converts an integer value to the
    /// </summary>
    /// <param name="value">Integer to convert</param>
    public static string ToString(int value)
    {
        StringBuilder builder = new StringBuilder();
        do
        {
            if (builder.Length > 0)
                value--;
            builder.Insert(0, _letters[value % _letters.Length]);
            value /= _letters.Length;
        } while (value > 0);

        return builder.ToString();
    }

    /// <summary>
    /// Converts spreadsheet column letters to their corresponding
    /// integer value.
    /// </summary>
    /// <param name="s">String to parse</param>
    public static int Parse(string s)
    {
        int result;
        if (!TryParse(s, out result))
            throw new ArgumentException();
        return result;
    }

    /// <summary>
    /// Converts spreadsheet column letters to their corresponding
    /// integer value.
    /// </summary>
    /// <param name="s">String to parse</param>
    /// <param name="result">The resulting integer value</param>
    public static bool TryParse(string s, out int result)
    {
        // Normalize input
        s = s.Trim().ToUpper();

        int pos = 0;
        result = 0;

        // Use lookup table to parse string
        while (pos < s.Length && !Char.IsWhiteSpace(s[pos]))
        {
            if (pos > 0)
                result++;

            string digit = s.Substring(pos, 1);
            int i = _letters.IndexOf(digit);
            if (i >= 0)
            {
                result *= _letters.Length;
                result += i;
                pos++;
            }
            else
            {
                // Invalid character encountered
                return false;
            }
        }
        // Return true if any characters processed
        return (pos > 0);
    }
}

Conclusion

This is a very simple class, but should be handy for cases where you need to work with spreadsheet column labels.

End-User License

Use of this article and any related source code or other files is governed by the terms and conditions of The Code Project Open License.

Author Information

Jonathan Wood

I'm a software/website developer working out of the greater Salt Lake City area in Utah. I've developed many websites including Black Belt Coder, Insider Articles, and others.