Excel VBA – Convert Column Number to English Alphabet Letter

Column Number to Alphabet Letter

Get vba code & Excel formula to get Column letter from column number, in here.

For example, If you pass ‘1’ to the function in this code, it will return as “A”. Similarly “B” for 2, “C” for 3 & so on.

Lets first see the Excel vba code & then its equivalent formula.

Also Read: How to convert Column alphabet Letter to number?

Excel VBA – To convert Column Number to Letter or Alphabet

Lets assume the same example as above to understand this. We are going to pass 26 to this function & get column letter ‘Z’.

Function Convert_Col_Number_To_Letter(ColumnNumber As Double) As String
    Dim sLetter As String
    
    'Split Address Letter & Row Number
    sLetter = Split(Cells(1, ColumnNumber).Address, "$")(1)

    'Return only the Column Letter
    Convert_Col_Number_To_Letter = sLetter
End Function

This function can be used inside another Macro code or in Excel worksheet also.

In any Excel sheet, enter “=Convert_Col_Number_To_Letter(26)” . Once you press “Enter” key it will return 26.

Excel Formula to Get Column Letter from Number

To understand this, lets assume we are passing number ’26’ & convert this into column letter.

Enter this formula in any of the Excel worksheet cell.

=SUBSTITUTE(ADDRESS(1,ColumnNumber,4),1,"")

In place of ‘ColumnNumber’ type 26. Press enter in Excel worksheet to get value ‘Z’.

Explanation of Formula used: 

• ADDRESS function with 4 as thirst parameter returns the absolute address of cell. i.e., Z1.
• SUBSTITUTE function replaces 1 with blank. So we get only the column letter.

Additional Reference:

• Stackoverflow – Discussion on How to convert a number to column letter. Read here.
• Microsoft Support article explaining the same concept with different logic. Read here.