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