# How to Convert Column Number to Letter in Excel (3 Methods)

## Method 1 – 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.

• Select a Cell:
• Choose the cell where you want the result to appear.
• Formula:
• The generic formula to convert a column number to a letter is as follows:
`=SUBSTITUTE(ADDRESS(1,col_number,4),"1","")`
• In your specific cell, enter this formula:
`=SUBSTITUTE(ADDRESS(1,B5,4),"1","")`

Here,

B5represents the cell reference containing the column number you want to convert.

• Press Enter:
• After entering the formula, press Enter.
• Result:
• You’ll get the associated letter address (e.g., A) corresponding to the column number (e.g., 1) in your dataset.
• Apply to Other Cells:
• Drag the formula down using the Fill Handle to convert other column numbers into letters.

Formula Breakdown:

• ADDRESS(1,B5,4) returns the cell address based on the given row (1) and column (from cell B5). The abs_num argument is set to 4 for relative referencing (without \$-signs).
• SUBSTITUTE(ADDRESS(1,B5,4),”1″,””) replaces the 1 in the address (e.g., A1) with nothing, resulting in the letter (e.g., A).

## Method 2 – VBA to Convert Column Number to Letter in Excel

• Open Visual Basic Editor:
• Press Alt + F11 on your keyboard or go to the Developer tab and click Visual Basic to open the Visual Basic Editor.
• Create a User-Defined Function (UDF):

• In the code window, click Insert and choose Module.

• Copy and paste the following code:
``````Public Function NumToLetter(ColNum)
End Function``````

• Save the code (instead of running it).
• Use the UDF:
• In a cell, enter the formula:

=NumToLetter(B5)

(Replace B5 with the cell reference containing the column number you want to convert.)

• Press Enter to get the associated letter address (e.g., A).
• Apply to Other Cells:
• Drag the formula down using the Fill Handle to convert other column numbers into letters.

## Method 3 – Excel’s Built-in Option

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

Steps:

• Click the File tab and select Options.

• In the Excel Options window, choose Formulas.
• Uncheck the R1C1 reference style box and click OK.

Related Articles

<< Go Back to Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Sanjida Ahmed

Sanjida Ahmed, who graduated from Daffodil International University with a degree in Software Engineering, has worked with SOFTEKO since 2021. She has written over 100 articles on Excel & VBA and, since 2022, has worked as the Project Manager of the Excel Extension Development Project in the Software Development Department. Since starting this software development, she has established an outstanding workflow encompassing a full SDLC. She always tries to create a bridge between her skills and interests in... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF