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

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","")

=SUBSTITUTE(ADDRESS(1,B5,4),"1","")

Here,

B5 – represents 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).

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):

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

=NumToLetter(B5)

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

Apply to Other Cells: Drag the formula down using the Fill Handle to convert other column numbers into letters.

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

Steps:

Your columns will now display letter addresses instead of numbers.

Download Workbook

You can download the practice workbook from here:

<< Go Back to Learn Excel

Related Articles