Documentation/Calc Functions/CELL
TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate
Function name:
CELL
Category:
Information
Summary:
Provides requested information on location, formatting properties, or contents of a cell.
Syntax:
CELL(Info Type[; Reference])
Returns:
Returns a value whose type depends on the nature of the information requested.
Arguments:
Info Type is a text string, or a reference to a cell containing a text string, that determines the type of information to be returned. Info Type is case-insensitive and must correspond to one of the following permitted options – "ADDRESS", "COL", "COLOR", "CONTENTS", "COORD", "FILENAME", "FORMAT", "PARENTHESES", "PREFIX", "PROTECT", "ROW", "SHEET", "TYPE", or "WIDTH". More detail about each of these options is given in the table below.
Reference is a reference to the cell for which information is to be returned. If Reference is omitted, information is returned for the cell in which the formula is located. Reference may be any of the following:
- A reference to a single cell.
- A reference to a cell range (for example, D6:E12).
- The name of a named range.
- The name of a database range.
Where Reference specifies multiple cells, CELL returns information for the top left of those cells.
Info Type string | Meaning |
---|---|
ADDRESS | Returns the absolute address of the cell. For example:
The above examples assume the default setting of Calc A1 for the Formula syntax menu in the ▸ ▸ ▸ dialog ( ▸ ▸ ▸ on macOS). |
COL | Returns the column number of the cell (column A is 1, column B is 2, and so on). For example:
|
COLOR | Returns a value indicating whether negative values are formatted in color in the cell (1=formatted in color, 0=not formatted in color). For example:
|
CONTENTS | Returns the contents of the cell, without any formatting. For example:
|
COORD | Returns the complete cell address using a legacy notation, in which the sheet position is indicated by a letter (A indicates the spreadsheet at the left of the sheet tab area, B the next sheet to the right, and so on). For example:
The "COORD" option for the Info Type argument is not a requirement of ODF 1.2. |
FILENAME | Returns the file name and the name of the sheet in which the cell is located. For example:
|
FORMAT | Returns a character string that indicates the cell’s number format. The possible return values are defined in a separate table below. |
PARENTHESES | Returns 1 if the cell's format code contains a left parenthesis and returns 0 otherwise. For example:
|
PREFIX | Returns a single character string indicating the alignment of text in the cell, coded as follows:
For example:
Returns the null character (Unicode U+0000) if the cell does not contain a string. |
PROTECT | Returns the status of cell protection for the cell. 1 indicates that the cell is protected, while 0 indicates that the cell is not protected. For example:
Note that cell protection is only effective after the current sheet has been protected. Cell protection may be enabled while a sheet is not protected. |
ROW | Returns the row number of the cell. For example:
|
SHEET | Returns the number of the referenced sheet, 1 indicates the sheet at the left of the sheet tab area, 2 the next sheet along to the right, and so on. For example:
|
TYPE | Returns the type of data in the cell. One of three possible single‑character strings – b (for blank) for an empty cell; l (for label) if the cell contains text; v (for value) otherwise. For example:
The formula |
WIDTH | Returns the width of the column in which the cell lies. The unit is the number of zero (0) characters that fit into the column based on the default font. For example:
|
Return value | Meaning |
---|---|
Numbers | |
, (comma) | Indicates that the cell is formatted to display numbers with thousands separators. The number of decimal places specified by the cell format is appended after the comma. For example:
|
F | Indicates that the cell is formatted to display numbers without thousands separators. The number of decimal places specified by the cell format is appended after the letter F. For example:
|
C | Indicates that the cell is formatted to display numbers in a currency format. The number of decimal places specified by the cell format is appended after the letter C. For example:
|
S | Indicates that the cell is formatted to display numbers in scientific (exponential) representation. The number of decimal places specified by the cell format is appended after the letter S. For example:
|
P | Indicates that the cell is formatted to display percentages. The number of decimal places specified by the cell format is appended after the letter P. For example:
|
Dates | |
D1 | Date formats comprising day, month, and year values. For example:
|
D2 | Date formats comprising day and month (abbreviated alphabetical name) values. For example:
|
D3 | Date formats comprising month and year values. For example:
|
D4 | Date and time formats comprising second, minute, hour, day, month, and year values (without AM/PM indicator). For example:
|
D5 | Date format comprising day and month (numerical) values. For example:
|
D6 | Time formats including second, minute, and hour values, with AM/PM indicator. For example:
|
D7 | Time formats including minute and hour values, with AM/PM indicator. For example:
|
D8 | Time formats including second, minute, and hour values, with no AM/PM indicator. For example:
|
D9 | Time formats including minute and hour values, with no AM/PM indicator. For example:
|
G | All other date and time formats. For example:
|
Additional notes
A - (minus) is appended at the end of the returned string when the cell is formatted to display negative numbers in colour. For example:
() (parentheses) are appended at the end of the returned string when the cell’s format code includes a left parenthesis. For example:
|
- If Info Type does not correspond to one of the expected strings, then CELL reports an invalid argument error (Err:502).
- If Reference does not correspond to a valid reference, the CELL reports a #REF! error.
- If Info Type is set to “CONTENTS” and Reference is omitted, then CELL reports a circular reference error (Err:522).
Additional details:
None
Examples:
See examples within the tables in the #Arguments: section above.
Related LibreOffice functions:
None.
ODF standard:
Related (or similar) Excel functions:
CELL