Formulas‎ > ‎Formulas‎ > ‎

Cell formula to display content, position and formatting of cell

posted Jun 11, 2011, 9:04 AM by Ayush Jain   [ updated Jun 11, 2011, 10:51 AM ]
Sometimes we have many questions in mind like :

How to know the cell protection status - Locked or Unlocked ?
How to know the width of the cell ?
How to know alignment of cell ?
How to identify row & Col number of cell ?
How to know the worksheet name, filename & full path of given cell ?
& many more questions

The answer of all above questions is CELL function of excel :
--------------------------------------------------------------------------------
DEFINITION:- This function examines a cell and returns the information about the formatting, location, or contents of the first cell
--------------------------------------------------------------------------------
SYNTAX :- '=CELL("TypeOfInfoRequired",CellToTest)

"TypeOfInfoRequired" is : address, col, color, contents, filename, format, parentheses, prefix, protect, row, type, width

CellToTest is the cell to be tested :- A3, A20, C15 . . . . . .. .
--------------------------------------------------------------------------------
EXAMPLES : Let's assume the CELL D3 in below examples

To know width of cell D3 :-                                         =CELL("width",D3)

To know the row number :-                                         =CELL("row",D3)

To know the column number :-                                    =CELL("col",D3)

To know the cell value or contents :-                            =CELL("contents",D3)

To know the cell address :-                                         =CELL("address",D3)

To know the filename containing the Cell :-                   =CELL("filename",D3)
(Returns the Full Path of file with worksheet name)

To know the cell protection :-                                       =CELL("protect",D3)
(shown as 1 for a locked, 0 for unlocked.)

To know the Type of Entry in the cell :                          =CELL("type",D3)
(Shown as b for blank, l for text, v for value.

To know the cell alignment :                                         =CELL("prefix",D3)
(Shown as ' for left, ^ for centre, " for right.
Nothing is shown for numeric entries.)

Download the file enclosed in the post for more examples
--------------------------------------------------------------------------------
APPLICATION OF THE FUNCTION :
Some of you must be wondering where we can you the cell function :

Before sending the file to user or client , you can make check list to validate the cell formatting , content and position using these formulas.

--------------------------------------------------------------------------------
BONUS :

How to extract the filename/workbookname from the fullpath returned by CELL function :

=MID(CELL("filename"),FIND("[",CELL("filename"))+1,FIND("]",CELL("filename"))-FIND("[",CELL("filename"))-1)
Ĉ
Ayush Jain,
Jun 11, 2011, 10:25 AM
Comments