Documentation/Calc Functions/CELL

From The Document Foundation Wiki
Jump to: navigation, search

Function name:

CELL

Category:

Information

Summary:

Returns information on address, formatting or contents of a cell.

Syntax:

CELL("InfoType"[; Reference])

Returns:

Arguments:

InfoType is the character string that specifies the type of information. The character string is always in English. Upper or lower case is optional.

InfoType Meaning
COL Returns the number of the referenced column.
ROW Returns the number of the referenced row.
SHEET Returns the number of the referenced sheet.
ADDRESS Returns the absolute address of the referenced cell.
FILENAME Returns the file name and the sheet number of the referenced cell.
COORD Returns the complete cell address in Lotus™ notation.
CONTENTS Returns the contents of the referenced cell, without any formatting.
TYPE Returns the type of cell contents.

b = blank. empty cell
l = label. Text, result of a formula as text
v = value. Value, result of a formula as a number

WIDTH Returns the width of the referenced column. The unit is the number of zeros (0) that fit into the column in the default text and the default size.
PREFIX Returns the alignment of the referenced cell.

' = align left or left-justified
" = align right
^ = centered
\ = repeating (currently inactive)

PROTECT Returns the status of the cell protection for the cell.

1 = cell is protected
0 = cell is not protected

FORMAT Returns a character string that indicates the number format.

, = number with thousands separator
F = number without thousands separator
C = currency format
S = exponential representation, for example, 1.234+E56
P = percentage
In the above formats, the number of decimal places after the decimal separator is given as a number. Example: the number format #,##0.0 returns ,1 and the number format 00.000% returns P3
D1 = MMM-D-YY, MM-D-YY and similar formats
D2 = DD-MM
D3 = MM-YY
D4 = DD-MM-YYYY HH:MM:SS
D5 = MM-DD
D6 = HH:MM:SS AM/PM
D7 = HH:MM AM/PM
D8 = HH:MM:SS
D9 = HH:MM
G = All other formats
- (Minus) at the end = negative numbers are formatted in color () (brackets) at the end = there is an opening bracket in the format code

COLOR Returns 1, if negative values have been formatted in color, otherwise 0.
PARENTHESES Returns 1 if the format code contains an opening bracket (, otherwise 0.

Reference (list of options) is the position of the cell to be examined. If Reference is a range, the cell moves to the top left of the range. If Reference is missing, LibreOffice Calc uses the position of the cell in which this formula is located. Microsoft Excel uses the reference of the cell in which the cursor is positioned.

Additional details:

None

Examples:

C D
2 Column1 Column2
3 -5 -3
4 -2 0
5 -1 1
6 0 3
7 2 4
8 4 10/08/01
9 6 -$11.11
Formula Description Returns
=CELL("COL";D2) The function returns the number of the referenced column. 4
=CELL("ROW";D2) The function returns the number of the referenced row. 2
=CELL("Sheet";Sheet3.D2) The function returns the number of the referenced sheet. 3
=CELL("ADDRESS";D2) The function returns the absolute address of the referenced cell. $D$2
=CELL("ADDRESS";Sheet3.D2) The function returns the absolute address of the referenced cell. $Sheet3.$D$2
=CELL("ADDRESS";'X:\dr\test.ods'#$Sheet1.D2) The function returns the absolute address of the referenced cell. 'file:///X:/dr/test.ods'#$Sheet1.$D$2
=CELL("FILENAME";D2) where the formula in the current document X:\dr\own.ods is located in Sheet1. The function returns the file name and the sheet number of the referenced cell. 'file:///X:/dr/own.ods'#$Sheet1
=CELL("FILENAME";'X:\dr\test.ods'#$Sheet1.D2) where the formula in the current document X:\dr\own.ods is located in Sheet1. The function returns the file name and the sheet number of the referenced cell. 'file:///X:/dr/own.ods'#$Sheet1
=CELL("COORD"; D2) The function returns the complete cell address in LotusTM notation. $A:$D$2
=CELL("COORD"; Sheet3.D2) The function returns the complete cell address in LotusTM notation. $C:$D$2
=CELL("CONTENTS";D2) The function returns the content of the referenced cell, without any formatting. 0
=CELL("TYPE";C2) Here, the function returns the label 'l' indicating the presence of a text value in the referenced cell. l
=CELL("TYPE";C3) Here, the function returns the label 'l' indicating the presence of a text value or a result of a formula as a text in the referenced cell. v
=CELL("TYPE";C3) Here, the function returns the label 'v' indicating the presence of a numeric value or a result of a formula as a number in the referenced cell. v
=CELL("WIDTH";D2) under default size The function returns width of the referenced column. The unit is the number of zeros (0) that fit into the column in the default text and the default size. Its default value is 11. 11
=CELL("PREFIX";D6) '
=CELL("PROTECT";D2) 1
=CELL("FORMAT";D8) D1
=CELL("COLOR";D9) 1
=CELL("PARENTHESES";D4) 0

Related LibreOffice functions:

None

ODF standard:

Section 6.13.3, part 2

Equivalent Excel functions:

CELL