This Excel tutorial explains how to use Excel Address Function and how to convert column number to column letter.
Excel Address Function
Excel Address Function is used to combine column number and row number to return Cell address in Text format.
Excel Address Function is most useful is converting column number to column letter, or use with Indirect Function to convert the Text address to Range Object.
Syntax of Excel Address Function
ADDRESS( row, column, [abs_num], [a1], [sheet_text] )
| row | Row number in number format | ||||||||||
| column | Column number in number format | ||||||||||
| [abs_num] | Optional, the style of Cell address
|
||||||||||
| [a1] | Optional, style of Referencing
|
||||||||||
| [sheet_text] | Add sheet name in the prefix of Cell address. You can type whatever you want, not necessary the worksheet name, it only puts this text in the address prefix. |
Example of Excel Address Function
| Formula | Value | Explanation |
| =ADDRESS(1,1) | $A$1 | Row 1 Column 1 = A1 |
| =ADDRESS(1,1,4) | A1 | Return A1 without absolute $ |
| =ADDRESS(1,1,4,0) | R[1]C[1] | Return R1C1 style of A1 |
| =ADDRESS(1,1,4,,”Sheet1″) | Sheet1!A1 | Add sheet name in address prefix |
Use Excel Address Function to convert column number to column letter
One important use of Address Function is to convert column number to column header.
For example, you have a column number 4, you want to convert to letter D.
| Formula | Value | Explanation | |
| Step 1 | =ADDRESS(1,4,4) | D1 | Put a dummy row number 1 in address, return relative reference |
| Step 2 | =SUBSTITUTE(ADDRESS(1,4,4),1,””) | D | Replace 1 as nothing |
After you have got the column letter, you can use it in another formula.
The below example shows how to get the D5 value using Indirect Function, which turns Text “D5” into actual Range value.
=INDIRECT(SUBSTITUTE(ADDRESS(1,4,4),1,"")&"5")
Recently I have answered a question in Microsoft Community that uses this skill, click here to read more.
If you want to know how it can be done in VBA, click here to read my other article.
Use Excel Column Function to convert column letter to column number
You can use Column Function to convert column letter to column number, which converts a Range to a column number. Since the Function argument is a Range, use indirect Function to convert the column letter to an actual Range. Similar to Address Function, put dummy row number 1 in the formula.
| Formula | Result |
| =COLUMN(INDIRECT(“A”&1)) | 1 |
| =COLUMN(INDIRECT(“Z”&1)) | 26 |