# Convert column number to letter

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

To convert a column number to an Excel column letter (e.g. A, B, C, etc.) you can use a formula based on the ADDRESS and SUBSTITUTE functions.

In the example shown, the formula in C5 is:

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

### How this formula works

The first step is to construct an address that contains the column number. We do this with the ADDRESS function, by providing 1 for row number, a column number from B5, and 4 for the abs_num argument (to get a relative reference):

ADDRESS(1,B5,4)

With this information, ADDRESS returns the text "A1".

Next ,we use SUBSTITUTE to strip out the number 1 like this:

=SUBSTITUTE("A1","1","")

We can confidently look for "1" and replace with "" in all cases, because the row number is hard-coded as 1 inside the ADDRESS function.

## Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.