Excel Address Function to convert column number to letter

This tutorial explains how to use Excel Address Function and to convert column number to column letter.

What does Excel Address Function do?

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.

Outbound References

https://support.office.com/en-us/article/address-function-47657e92-81ae-47f8-87cd-62d4f30c774d?ui=en-US&rs=en-US&ad=US&fromAR=1

 

 

Excel Address Function to convert column number to letter