Information Functions: CELL and INFO, etc.
CELL returns information about a specific cell. INFO returns information about the operating environment. The various IS- functions at the end of the post are also of interest.
CELL(info_type, reference)
CELL returns information about the formatting, location, or contents of the reference cell. (It uses the upper-left cell if the reference is a multi-cell range). info_type includes “address”, “row”, “col”, “contents”, “filename”, “format”, and “protect”.
Syntax: CELL(info_type, reference)
Arguments:
Info_type (required): A text value that specifies what type of cell information you want to return. The list below shows the allowed values of Info_type and the corresponding results.
Reference (required): The cell that you want information about.
Info_type values
The following list describes the text values that you can use for the info_type argument. Enter these values in the CELL function inside quotes (” “), or as a reference to another (text) cell.
Info_type Returns
“address” Reference of the first cell in reference, as text.
“col” Column number of the cell in reference.
“color” The value 1 if the cell was formatted in colour for negative values; otherwise, 0 (zero).
“contents” Value of the upper-left cell in reference; not a formula.
“filename” Full path and name of the file that contains reference. Returns empty text (“”) if the worksheet that contains reference is not yet saved.
“format” Text value corresponding to the number format of the cell. We list the text values for various formats below. Returns “-” at the end of the text value if the cell is formatted in colour for negative values. Returns “()” at the end of the text value if the cell format has parentheses for positive or all values.
“parentheses” The value 1 if the cell format has parentheses for positive or all values; otherwise returns 0.
“prefix” Text value corresponding to the “label prefix” of the cell. Returns:
- single quotation mark (‘) if the cell contains left-aligned text,
- double quotation mark (“) if the cell contains right-aligned text,
- caret (^) if the cell contains centred text,
- backslash (\) if the cell contains fill-aligned text, and
- empty text (blank string) if the cell contains anything else.
“protect” The value 0 if the cell is not locked; otherwise returns 1 for a locked cell.
“row” Row number of the cell in reference.
“type” A text value corresponding to the type of data in the cell. Returns
- “b” for blank if the cell is empty,
- “l” for label if the cell contains text (a constant or a formula that produces text), and
- “v” for value if the cell contains anything else.
“width” Returns the column width of the cell, rounded to an integer. Each unit of column width is equal to the width of one character in the default font size.
=CELL(info_type, ref) Examples
CELL format codes
The following table lists the values that the CELL function returns when the Info_type is “format” and the reference cell format is a built-in number format.
| If the Excel format is | =CELL(“format”, ref) returns |
| General | “G” |
| 0 | “F0” |
| 0.00 | “F2” |
| #,##0 | “,0” |
| #,##0.00 | “,2” |
| $#,##0_);($#,##0) | “C0” |
| $#,##0_);[Red]($#,##0) | “C0-“ |
| $#,##0.00_);($#,##0.00) | “C2” |
| $#,##0.00_);[Red]($#,##0.00) | “C2-“ |
| 0% | “P0” |
| 0.00% | “P2” |
| 0.00E+00 | “S2” |
| d-mmm-yy or dd-mmm-yy | “D1” |
| d-mmm or dd-mmm | “D2” |
| mmm-yy | “D3” |
| m/d/yy or m/d/yy h:mm or mm/dd/yy | “D4” |
| mm/dd | “D5” |
| h:mm:ss AM/PM | “D6” |
| h:mm AM/PM | “D7” |
| h:mm:ss | “D8” |
| h:mm | “D9” |
Note: If the info_type argument in the CELL function is “format” and you later apply a different format to the referenced cell, you must recalculate the worksheet (F9) to update the results of the CELL function.
=CELL(“format”, ref) Date Examples
Note that these generally refer to built-in number formats. Where there isn’t a built-in number format, the function returns “G” (General). There is some ambiguity, seen in cells A24 and A25 in the example: Both return “D2”, making the results unreliable.
INFO(type_text)
Syntax: INFO(type_text) returns information about the current operating environment.
Argument: Type_text (required): Text that specifies what type of information you want returned.
Type_text Returns:
“directory” Path of the current directory or folder specified in the Excel option, “At startup, open all files in:” (File menu > Options > Advanced > General).
“numfile” Number of worksheets in the open workbooks. Sometimes wildly inaccurate.
“origin” Returns the absolute cell reference of the top and leftmost cell visible in the window, based on the current scrolling position, as text prepended with “$A:”. This value is for Lotus 1-2-3 release 3.x compatibility.
“osversion” Current operating system version, as text.
“recalc” Current recalculation mode; returns “Automatic” or “Manual”.
“release” Version of Microsoft Excel, as text.
“system” Name of the operating environment: Macintosh = “mac”, Windows = “pcdos”
INFO Examples
Other Information Functions
ISBLANK, ISERR, ISERROR, ISLOGICAL, ISNA, ISNONTEXT, ISNUMBER, ISREF and ISTEXT. All return TRUE if the condition is met, and FALSE otherwise.