In this article, we will show you how to convert the column number to letter in Excel in 3 easy and effective ways.
How to Convert Column Number to Letter in Excel: 3 Easy Ways
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,
=SUBSTITUTE(ADDRESS(1,col_number,4),"1","")- So, in that cell, write the formula as,
=SUBSTITUTE(ADDRESS(1,B5,4),"1","")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.
Formula Breakdown:
- ADDRESS(1,B5,4)
- Output: A1
- Explanation: The ADDRESS function returns the cell address based on the given row and column. We provided row number 1 and the column number from B5 to construct the address, and to get a relative reference, we set 4 for the abs_num argument.
- abs_num = 4 is a constant value. You must set the value as 4, otherwise, the cell address will display with $-signs.
- SUBSTITUTE(ADDRESS(1,B5,4),”1″,””) -> becomes
- SUBSTITUTE(A1,”1″,””)
- Output: A
- Explanation: The SUBSTITUTE function replaces 1 with nothing (“”) from A1, hence returns A.
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: VBA to Convert Column Number to Letter in Excel
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: Column Letter to Number Converter in Excel
Download Workbook
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.
Related Articles
- How to Find Column Index Number in Excel
- How to Return Column Number of Match in Excel
- How to Find Column Number Based on Value in Excel
- How to Change Column Name from ABC to 1 2 3 in Excel
- Find Value in Row and Return Column Number Using VBA in Excel
- How to Use VBA Range Based on Column Number in Excel
