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

Formula to Convert Column Number to Letter in Excel

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

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


Similar Readings


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

VBA code to Convert Column Number to Letter in Excel

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.

VBA to Convert Column Number to Letter in Excel

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.

Excel’s Built-in Option to Convert Column Number to Letter

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

Applying Excel’s Built-in Option to Convert Column Number to Letter

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

Sanjida Ahmed

Sanjida Ahmed

Hello World! This is Sanjida, an Engineer who is passionate about researching real-world problems and inventing solutions that haven’t been discovered yet. Here, I try to deliver the results with explanations of Excel-related problems, where most of my interpretations will be provided to you in the form of Visual Basic for Applications (VBA) programming language. Being a programmer and a constant solution seeker, made me interested in assisting the world with top-notch innovations and evaluations of data analysis.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo