Get cell address in MS Excel


How to get the various format of a cell address in excel
Sometimes we may need to get the address of cell in various formats like “the column no for a cell like 6” or “cell position like $C$6” or “column name like AL” etc
All of the above can be achieved using the CELL or INDIRECT and ADDRESS functions.
Find some examples below.

Get_Column_no as cell address

Get the column number of a cell
Use the formula

=CELL("col",F9)

This will give the column number of F9 i.e 6

Get Cell Address

Get cell address in the form of $C$6
Use the formula

=ADDRESS(1,3)   where 1 is the row number and 3 is the column number of the cell

This will give the address $C$1

Get_Column_name

Get the column name of a cell like “AC” etc
Use the formula

=MID(ADDRESS(1,38),2,LEN(ADDRESS(1,38))-3)   where 38 is the column number

The above formula will give us the column name of the column 38 i.e “AL”

Get_Column_no_indirect

Get the column no when cell address is in another cell
Use the formula (check usage of INDIRECT)

=COLUMN(INDIRECT($G$10))  where $G$10 holds the cell address

If cell $G$10 holds the value “B1” then the result of the above formula will be 2
And if cell $G$10 holds the value “$AL$3” then the result of the above formula will be 38
Note:- Please notice that INDIRECT understands $AL$3 or AL3  as the same

There are many variations that can be achieved using the ADDRESS, CELL and INDIRECT  functions as per the requirement. We will show some live examples as to “why you may need to get various forms of a cell address” with some practical examples soon

HAPPY EXCEL LEARNING!!

Leave a comment

Your email address will not be published. Required fields are marked *