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