< Go Back

Convert column number to letter

CONVERT COLUMN NUMBERS TO LETTERS

This article will provide you with steps on how to convert column Number to Letters. For example, converting Number 27 to letter AA.

In order to convert an Excel column Number to an Excel column Letter, you can make use of the ADDRESS and SUBSTITUTE functions-based formula.

The formula used on the above example in C5 is SUBSTITUTE (ADDRESS (10, B5, 4),"10","")

What are SUBSTITUTES?

In Excel, the SUBSTITUTE function is used to replace texts in a particular string by matching them all. Example when you have a string like =SUBSTITUTE (“982-455-7862”,” –“,””)   it returns as “9824557862” the dash get removed. Mind you, the SUBSTITUTE does not support wildcards. It only replaces text based on contents.

What is ADDRESS?

The ADDRESS as an Excel function returns the address for a given row and column number in a cell. Example when you have =ADDRESS (1, 1) it returns as $A$1. The ADDRESS can also be used to construct a reference on a cell inside a formula.

Purpose

USING THE SUBSTITUTE AND ADDRESS FUNCTIONS TO CONVERT NUMBERS INTO EXCEL LETTERS.

Example

  • First and foremost, in other to construct an address that is made up of column numbers,

 

We do the following: on the ADDRESS function, we provide 10 for row numbers, we provide a column number for B5 , and number 4 for the abs_num argument so as to get a relative reference. i.e. ADDRESS(10,B5,4)

With the information provided above, ADDRESS returns it as “J10”

 

Furthermore, we make use of the substitute to strip out the number 10.

Here is how it’s done: In all cases, we can confidently locate “10” and then replace it with “” i.e. =SUBSTITUTE (“J10”,”10”,””). This is because the number on the row is hard-coded as 10 inside the ADDRESS function.

 

Following the steps listed above, you have successfully converted Column Number to Letters.

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar