CELL - Display Information About a Cell

What it Does:-

This function examines a cell and displays information about the contents, position and formatting.

Syntax:-

=CELL("TypeOfInfoRequired",CellToTest)

The TypeOfInfoRequired is a text entry which must be surrounded with quotes " ".

Usuage:-

The cell to test is B1 with contents :

ABCD

The cell address:$B$1 can be determined as

=CELL("address",B1)

The column number:2 can be determined as

=CELL("col",B1)

The row number:1 can be determined as

=CELL("row",B1)

The contents of the cell:ABCD can be determined as

=CELL("contents",B1)

The type of entry in the cell:v (*Refer index below) can be determined as

=CELL("type",B1)

The width of the cell:20 can be determined as

=CELL("width",B1)

The number format of the cell:G (**Refer index below) can be determined as

=CELL("format",B1)

Formatted for braces()on +ve values (1-Yes, 0-No):0 can be determined as

=CELL("parentheses",B1)

Formatted for coloured negatives (1-Yes, 0-No):0 can be determined as

=CELL("color",B1)

Type of cell protection(1-locked,0-unlocked):0 can be determined as

=CELL("protect",B1)

The filename containing the cell:C:\[test.xlsx]Sheet1 can be determined as

=CELL("filename",B1)

Indexes

*The type of entry in the cell:

b - Blank
l - Text
v - Value

**The number format of the cell:

G - General
F0 - 0
F2 - 0.00
,0 - #,##0
,2 - #,##0.00
C0 - $#,##0_);($#,##0)
C0- - $#,##0_);[Red]($#,##0)
C2 - $#,##0.00_);($#,##0.00)
C2- - $#,##0.00_);[Red]($#,##0.00)
P0 - 0%
S2 - 00E+00
G - # ?/? or # ??/??
D4 - m/d/yy or m/d/yy h:mm or mm/dd/yy
D1 - d-mmm-yy or dd-mmm-yy
D2 - d-mmm or dd-mmm
D3 - mmm-yy
D5 - mm/dd
D7 - h:mm AM/PM
D6 - h:mm:ss AM/PM
D9 - h:mm
D8 - h:mm:ss

Example:-

The following example uses the =CELL() function as part of a formula which extracts the filename.

=MID(CELL("filename"),FIND("[",CELL("filename"))+1,FIND("]",CELL("filename"))-FIND("[",CELL("filename"))-1)

Output of the above formula is :- test.xlsx