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

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,
`=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:

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