Excel Address Function to convert column number to letter

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

Value Explanation
1 (default) Absolute referencing.
For example: $A$1
2 Relative column; absolute row
For example: A$1
3 Absolute column; relative row
For example: $A1
4 Relative referencing.
For example: A1

 

[a1] Optional, style of Referencing

Value Explanation
TRUE (default) A1 style referencing
FALSE R1C1 style 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

 

Leave a Reply

Your email address will not be published.