VBA to Convert Column Number to Letter in Excel (3 Methods)

In this article, we will demonstrate how to convert a column number to its corresponding column letter in Excel with VBA code using 3 methods: converting a specific column number to its column letter, converting a user-input column number to its letter, and creating a UDF (User-Defined Function) to convert a column number to its letter.

Method 1 – VBA to Convert a Specific Column Number to Its Letter in Excel

Steps:

Press Alt + F11 on your keyboard (or go to the Developer tab -> Visual Basic) to open the Visual Basic Editor.

Click Insert -> Module.

Copy the following code and paste it into the code window that opens:

Sub ColNumToLetter()
Dim ColNumber As Long
Dim ColLetter As String
'Input Column Number
ColNumber = 200
'Convert To Column Letter
ColLetter = Split(Cells(1, ColNumber).Address, "$")(1)
'Display Result
MsgBox "Column " & ColNumber & " = Column " & ColLetter
End Sub

This code will convert column number 200 to its associated letter address.

To run the code, either press F5 on your keyboard, or from the menu bar select Run -> Run Sub/UserForm, or click on the small Play icon in the sub-menu bar.

A pop-up message box displaying the letter address (GR) of the column number (200) is shown.

Method 2 – Switching a Column Number to Its Letter with a User-Defined Function (UDF)

Steps:

As before, open the Visual Basic Editor and Insert a Module in the code window.
Copy the following code and paste it In the code window.

Public Function NumToLtr(ColNum)
NumToLtr = Split(Cells(1, ColNum).Address, "$")(1)
End Function

In the first Method, we created a Sub Procedure for the VBA program to run. Here, we’ve created a User Defined Function (UDF).

After writing the code, instead of clicking the Run button from the menu bar, click Save.
Go back to the worksheet, and in the output cell enter the following function you just created, passing the cell reference number to convert (in this case, cell B5).

=NumToLtr(B5)

Press Enter.

The associated letter address (A) of the column number (1) is returned.

Drag the row down using the Fill Handle to apply the UDF to the rest of the cells.

Using The GoTo Statement

The above procedure can also be performed with the GoTo Statement.

The VBA code to convert a column number to its associated letter using the GoTo Statement is:

Public Function Num_Letter(ByVal iColNum As Integer) As String
Dim SLetter As String
   On Error GoTo iError
   Num_Letter = Left(Cells(1, iColNum).Address(False, False), _
                  Len(Cells(1, iColNum).Address(False, False)) - 1)
   Exit Function
iError:
   Call MsgBox(Err.Number & " - " & Err.Description)
End Function

As above, call the UDF in the dataset, pass the cell reference number as the argument, press Enter and drag the row to convert all the column numbers to their corresponding column letters.

Using The Do-While Loop

The Do-While Loop is another way to convert column numbers to letters with VBA.

The VBA code to convert a column number to its associated letter with the Do-While Loop is:

Function NumberToLetter(iCol As Long) As String
   Dim a As Long
   Dim b As Long
   a = iCol
   NumberToLetter = ""
   Do While iCol > 0
      a = Int((iCol - 1) / 26)
      b = (iCol - 1) Mod 26
      NumberToLetter = Chr(b + 65) & NumberToLetter
      iCol = a
   Loop
End Function

Again, call the UDF in the dataset, pass the cell reference number as the argument, press Enter and drag the row to convert all the column numbers to letters.

Method 3 – VBA to Change a Column Number to Its Letter from User Input

Steps:

Same way as before, open the Visual Basic Editor from the Developer tab and Insert a Module in the code window.
Copy the following code and paste it in the code window:

Sub GetNumberFromUser()
    Dim ColNumber As Long
    Dim ColLetter As String
    'User Input: Column Number
    ColNumber = InputBox("Please Enter a Column Number")
    'Convert the user-entered Column Number To Column Letter
    ColLetter = Split(Cells(1, ColNumber).Address, "$")(1)
    'Display the Result
    MsgBox "Column Number: " & ColNumber & _
                        " = Column Letter: " & ColLetter
End Sub