Excel CELL Function
The Excel CELL function returns information about a cell in a worksheet. The type of information to be returned is specified as info_type. CELL can get things like address and filename, as well as detailed info about the formatting used in the cell. See below for a full list of information available.
- info_type - The type of information to return about the reference.
- reference - [optional] The reference from which to extract information.
Use CELL to extract a wide range of information about reference. The type of information to be returned is specified as info_type. Always wrap the info_type in double quotes. For the formula: =CELL("col", C10) will return 3. See below for a full list of info_types and a key to the codes that CELL returns when the info_type is format.
Note that when reference refers to more than one cell, CELL will return information about the first cell in reference.
The following info_types can be used with the CELL function:
|address||returns the address of the first cell in reference (as text).|
|col||returns the column number of the first cell in reference.|
|color||returns the value 1 if the first cell in reference is formatted using color for negative values; or zero if not.|
|contents||returns the value of the upper-left cell in reference. Formulas are not returned. Instead, the result of the formula is returned.|
|filename||returns the file name and full path as text. If the worksheet that contains reference has not yet been saved, an empty string is returned.|
|format||returns a code that corresponds to the number format of the cell. See below for a list of number format codes. If the first cell in reference is formatted with color for values < 0, then "-" is appended to the code. If the cell is formatted with parentheses, returns "() - at the end of the code value.|
|parentheses||returns 1 the first cell in reference is formatted with parentheses and 0 if not.|
|prefix||returns a text value that corresponds to the label prefix - of the cell: a single quotation mark (') if the cell text os left-aligned, a double quotation mark (") if the cell text is right-aligned, a caret (^) if the cell text is centered text, a backslash () if the cell text is fill-aligned, and an empty string if the label prefix is anything else.|
|protect||returns 1 if the first cell in reference is locked or 0 if not.|
|row||returns the row number of the first cell in reference.|
|type||returns a text value that corresponds to the type of data in the first cell in reference: "b" for blank when the cell is empty, "l" for label if the cell contains a text constant, and "v" for value if the cell contains anything else.|
|width||returns the column width of the cell, rounded to the nearest integer. A unit of column width is equal to the width of one character in the default font size.|
The following table is a key for the text codes that are returned from CELL when "format" is used for info_type.
|Format code returned||Format code meaning|
|G||# ?/? or # ??/??|
|D1||d-mmm-yy or dd-mmm-yy|
|D2||d-mmm or dd-mmm|
|D4||m/d/yy or m/d/yy h:mm or mm/dd/yy|