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.
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.
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.