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!!