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


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

Ayush Jain,
Jun 11, 2011, 10:25 AM