How to Convert Column Number to Letter in Excel (3 Ways)

In this article, we will show you how to convert the column number to letter in Excel in 3 easy and effective ways.

Download Workbook

You can download the free practice Excel workbook from here.

3 Easy Ways to Convert Column Number to Letter in Excel

In this section, you will learn how to convert column numbers to letters with the formula, VBA code and built-in option in Excel.

1. Formula to Convert Column Number to Letter in Excel

Consider the following dataset which we will be using as our example to convert column number to letter by applying the formula.

Steps:

• Select a cell that you want your result to show.
• The generic formula to convert column number to letter is,

• So, in that cell, write the formula as,

Here,

B5 = Cell reference number that holds the column number to convert into a letter

• Press Enter.

You will get the associated letter address (A) of the column number (1) in your dataset.

• Now drag the row down by Fill Handle to apply the formula to the rest of the cells to convert them into letters.

Read More: [Fixed] Excel Column Numbers Instead of Letters (2 Solutions)

Similar Readings

• VBA to Use Range Based on Column Number in Excel (4 Methods)
• How to Convert Column Letter to Number Chart in Excel (4 Ways)
• Excel VBA: Set Range by Row and Column Number (3 Examples)

2. VBA to Transform Column Number to Letter in Excel

Steps to convert the column number to letter in Excel with VBA are given below.

We will utilize a User-Defined Function (UDF) to convert the number.

Steps:

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

• In the pop-up code window, from the menu bar, click Insert -> Module.

• Copy the following code and paste it into the code window.

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

This is not a Sub Procedure for the VBA program to run, this is creating a User Defined Function (UDF). So, after writing the code, instead of clicking the Run button from the menu bar, click Save.

• Now go back to the worksheet of interest and write the function you just created with VBA code (Function NumToLetter in the first line of the code) and inside the parentheses of the NumToLetter function, pass the cell reference number that you want to convert to the letter (in our case, we pass Cell B5 inside the parentheses).

So our final formula refers,

=NumToLetter(B5)

• Press Enter.

You will get the associated letter address (A) of the column number (1) in your dataset.

• Now drag the row down by Fill Handle to apply the UDF to the rest of the cells to convert them into letters.

Read More: Excel VBA: Count Columns with Data (2 Examples)

3. Excel’s Built-in Option to Change Column Number to Letter

Excel has a built-in option to change the column number (shown below in the picture) to letter.

Steps:

• Click the tab File -> Options.

• From the pop-up Excel window, select Formulas -> uncheck the R1C1 reference style box -> OK.

Your columns will now have letter addresses instead of numbers.

Read More: How to Count Columns for VLOOKUP in Excel (2 Methods)

Conclusion

This article showed you how to convert the column number to letter in Excel in 3 different ways. I hope this article has been very beneficial to you. Feel free to ask if you have any questions regarding the topic.

Further Readings

• Perform VLOOKUP by Using Column Index Number from Another Sheet
• How to Find Column Index Number in Excel (2 Methods)
• Count One Column If Another Column Meets Criteria in Excel (4 Suitable Ways)
• Excel VBA: Get Row and Column Number from Cell Address (4 Methods)
• How to Return Column Number of Match in Excel (5 Useful Ways)
• How to Change Column Name from ABC to 1 2 3 in Excel