Column No to Column Letter in Excel/VSTO using C#

Question

How to find column's name or header?

For example if i select column 5 in excel means i want the result as "E". How to get the alphabet or letter corresponding to column no.

Please help me with the code

score 5 · Answer 1 · answered Mar 26 '10 at 13:11

5

public static string GetColumnName(int columnNumber)
{
    const string letters = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
    string columnName = "";

    while (columnNumber > 0)
    {
        columnName = letters[(columnNumber - 1) % 26] + columnName;
        columnNumber = (columnNumber - 1) / 26;
    }

    return columnName;
}

answered Mar 26 '10 at 13:11

LukeH

263,068
57
365
409

1

hmm.. and if there are more than 26 columns? AA, AB, AC etc – Ahmad Mar 30 '10 at 07:12
apologies..I realized this works.. http://stackoverflow.com/questions/181596/how-to-convert-a-column-number-eg-127-into-an-excel-column-eg-aa – Ahmad Mar 30 '10 at 08:24

Ahmad · Accepted Answer · 2010-03-30T07:54:43.093

2

What about using Application.ActiveCell.get_Address(true, true, Excel.AlReferenceStyle.xlA1, missing, missing) and then parse the result string or use a RegEx to get the column heading?

I simply used:

string location = Application.ActiveCell.get_Address(true, true, Excel.AlReferenceStyle.xlA1, missing, missing);
string tokens = x.Split("$".ToCharArray());
MessageBox.Show(String.Format("Column {0}", result[0]));

edited Mar 30 '10 at 07:54

answered Mar 30 '10 at 07:40

Ahmad

22,657
9
52
84

2

This code is wrong. Here's a corrected version: var location = sheet.Range["A1"].Offset[0, columnNumber - 1].Address[true, true, XlReferenceStyle.xlA1, Missing, Missing]; var tokens = location.Split('$'); return tokens[1]; – Phred Menyhert Jan 21 '12 at 17:35

score 1 · Answer 3 · answered Feb 19 '12 at 03:37

public static long GetColumnNumber(string columnName)
{
    int letterPos = 0;   
    long columnNumber = 0;
    for (int placeHolder = columnName.Length - 1; placeHolder >= 0; placeHolder--)
    {
        int currentSum = 1;
        for (int multiplier = 0; multiplier < placeHolder; multiplier++)
            currentSum *= 26;
        int letterValue = (int) columnName[letterPos];
        currentSum *= letterValue - 64;
        columnNumber += currentSum;
        if (letterPos != columnName.Length)
            letterPos++;
        //Console.WriteLine(((int)columnName[i]-64) + " = " + columnName[i]);
    }
        return columnNumber;
}

This method is working properly only when columnName is Capital string, for some reason b is not B. Hence column.Name.ToUpper() is required. — Jim, Aug 18 '15 at 08:52

score 0 · Answer 4 · answered Jan 22 '14 at 23:14

I use these two:

public string GetExcelColumn(int index)
{
    int quotient = index / 26;

    if (quotient > 0)
        return GetExcelColumn(quotient - 1) + (char)((int)'A' + (index % 26));
    else
        return "" + (char)((int)'A' + index);
}

static IEnumerable<string> GetExcelColumns()
{
    var alphabet = new string[]{""}.Union(from c in Enumerable.Range((int)'A', 26) select Convert.ToString((char)c));

    return from c1 in alphabet
            from c2 in alphabet
            from c3 in alphabet.Skip(1)                    // c3 is never empty
            where c1 == string.Empty || c2 != string.Empty // only allow c2 to be empty if c1 is also empty
            select c1 + c2 + c3;
}

score 0 · Answer 5 · answered Apr 15 '10 at 06:37

The following is a complete method which gives you the corresponding alphabet for an integer value that is passed.

private String Number2String(int number, bool isCaps)
    {
        int number1 = number / 27;
        int number2 = number - (number1 * 26);
        if (number2 > 26)
        {
            number1 = number1 + 1;
            number2 = number - (number1 * 26);
        }
        Char a = (Char)((isCaps ? 65 : 97) + (number1 - 1));
        Char b = (Char)((isCaps ? 65 : 97) + (number2 - 1));
        Char c = (Char)((isCaps ? 65 : 97) + (number - 1));
        string d = String.Concat(a, b);
        if (number <= 26)
            return c.ToString();
        else
            return d;
    }

score 0 · Answer 6 · edited May 23 '17 at 12:24

This works well in VBA by using a double replace, where R is a Single Cell Excel Range:

ColumnLetter = Replace(Replace(R.AddressLocal(ReferenceStyle:=1), "$", vbNullString), R.Row, vbNullString) It is based on the equivalent idea for use on a Worksheet. In a Cell Formula use this, it is even shorter:

=SUBSTITUTE(ADDRESS(1,COLUMN(M1),4),1,"")

This returns the letter M and works right up to Column XFD. The cell reference M1 can be any Range anywhere. The top left Column is returned for Ranges or more than one cell.

It gets the ADDRESS of the first Cell in the Column and then removes the trailing 1 by substituting a NullString for it. (The 4 in the ADDRESS makes sure that the Address is returned as a Relative Address, i.e. one without and $ signs in it.)

Thanks to barry houdini who set me off on the quest for a good answer to this.

Column No to Column Letter in Excel/VSTO using C#

6 Answers6

Linked