< Go Back

Copy value from every nth row

CONVERT COLUMN NUMBERS TO LETTERS

This article will provide you with steps on how to convert column Number to Letters.

EXAMPLE

For example, converting Number 27 to letter AA.

 Figure 1. Using of the ADDRESS and SUBSTITUTE function to convert Numbers to Letters

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

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

HERE IS HOW THE FORMULA WORKS

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

Figure 2. construct an address 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”

Figure 3. ADDRESS Formula

  • 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
This blog is one way to figure it out. A 20 minute chat session with an expert is a better way.Get step-by-step guidance on your question

Leave a Comment

avatar