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

=CELL("address",B1)

The column number:

2

=CELL("col",B1)

The row number:

1

=CELL("row",B1)

The contents of the cell:

ABCD

=CELL("contents",B1)

The type of entry in the cell*:

v

=CELL("type",B1)

The width of the cell:

20

=CELL("width",B1)

The number format of the cell**:

G

=CELL("format",B1)

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

0

=CELL("parentheses",B1)

Formatted for coloured negatives (1-Yes, 0-No):

0

=CELL("color",B1)

Type of cell protection(1-locked,0-unlocked):

0

=CELL("protect",B1)

The filename containing the cell:

C:\[blog.xlsx]Sheet1

=CELL("filename",B1)

*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 :-  blog.xlsx