How to use the CELL function
The CELL function gets information about the formatting, location, or the contents of a cell.
The formula example above in cell C3 returns a value determined by the contents of the cell.
| Return value | Cell content |
| l | text |
| b | blank |
| v | anything else |
Formula in cell C3:
Table of Contents
1. Cell Function Syntax
CELL(info_type, [reference])
2. Cell Function Arguments
| info_type | Required. A value that determines the type of cell information the function returns. |
List of Info_type arguments and what they do.
| 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 is formatted in color for negative values; otherwise returns 0 (zero). |
| "contents" | Value of the upper-left cell in reference; not a formula. |
| "filename" | Filename (including full path) of the file that contains reference, as text. Returns empty text ("") if the worksheet that contains reference has not yet been saved. |
| "format" | Text value corresponding to the number format of the cell. The text values for the various formats are shown in the following table. Returns "-" at the end of the text value if the cell is formatted in color for negative values. Returns "()" at the end of the text value if the cell is formatted with parentheses for positive or all values. |
| "parentheses" | The value 1 if the cell is formatted with 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 centered text, backslash (\) if the cell contains fill-aligned text, and empty text ("") if the cell contains anything else. |
| "protect" | The value 0 if the cell is not locked; otherwise returns 1 if the cell is locked. |
| "row" | Row number of the cell in reference. |
| "type" | 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 a text constant, and "v" for value if the cell contains anything else. |
| "width" | Column width of the cell, rounded off to an integer. Each unit of column width is equal to the width of one character in the default font size. |
The second argument in the CELL function is [Reference].
| [Reference] | Optional. The cell that you want information about. If omitted, the information specified in the Info_type argument is returned for the last cell that was changed. |
Info_type "format" returns the following codes for different cell formatting settings.
| Excel format | Returns |
| General | "G" |
| 0 | "F0" |
| #,##0 | ",0" |
| 0.00 | "F2" |
| #,##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" |
| # ?/? or # ??/?? | "G" |
| m/d/yy or m/d/yy h:mm or mm/dd/yy | "D4" |
| d-mmm-yy or dd-mmm-yy | "D1" |
| d-mmm or dd-mmm | "D2" |
| mmm-yy | "D3" |
| mm/dd | "D5" |
| h:mm AM/PM | "D7" |
| h:mm:ss AM/PM | "D6" |
| h:mm | "D9" |
| h:mm:ss | "D8" |
3. CELL function - example
This example demonstrates how to return a type value using the CELL function. The type value tells you if a cell is empty, contains text, or a number.
Formula in cell C3:
CELL(info_type, [reference])
info_type - type
[reference] - B3
"b" - blank, cell is empty.
"l" - label (text)
"v" - value (number)
4. How to identify formatting of a specific cell
The image above shows the CELL function calculating a code based on a cell reference, in this example, cell B3.
Formula in cell C3:
CELL(info_type, [reference])
You can verify the CELL function out by selecting cell B3. Press CTRL + 1, a dialog box appears.
The category is "General" which is the same as "G". I will describe below how to get the formatting code based ont the output from the CELL function.
The formula in cell D3 translates the code returned from the CELL function to something easier to understand. The table in cell range B9:C30 is used to match the code to the correct format.
Formula in cell D3:
4.1 Explaining formula in cell D3
Step 1 - Compare values to condition
The equal sign compares values in an Excel formula, the result is aboolean value TRUE or FALSE.
$C$9:$C$30=C3
becomes
{"G"; "F0"; ",0"; "F2"; ",2"; "C0"; "C0-"; "C2"; "C2-"; "P0"; "P2"; "S2"; "G"; "D4"; "D1"; "D2"; "D3"; "D5"; "D7"; "D6"; "D9"; "D8"}="G"
and returns
{TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}.
Step 2 - Filter matching values
The FILTER function extract values/rows based on a condition or criteria.
FILTER(array, include, [if_empty])
FILTER($B$9:$B$30,$C$9:$C$30=C3)
becomes
FILTER({"General"; 0; "#,##0"; "0.000"; "#,##0.00"; "$#,##0_); ($#,##0)"; "$#,##0_); [Red]($#,##0)"; "$#,##0.00_); ($#,##0.00)"; "$#,##0.00_); [Red]($#,##0.00)"; "0%"; "0.00%"; "0.00E+00"; "# ?/? or # ??/??"; "m/d/yy or m/d/yy h:mm or mm/dd/yy"; "d-mmm-yy or dd-mmm-yy"; "d-mmm or dd-mmm"; "mmm-yy"; "mm/dd"; "h:mm AM/PM"; "h:mm:ss AM/PM"; "h:mm"; "h:mm:ss"}, {TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE})
and returns
{"General"; "# ?/? or # ??/??"}
Step 3 - Concatenate values
The TEXTJOIN function allows you to combine text strings from multiple cell ranges and also use delimiting characters.
TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)
TEXTJOIN(", ",TRUE,FILTER($B$9:$B$30,$C$9:$C$30=C3))
becomes
TEXTJOIN(", ",TRUE, {"General"; "# ?/? or # ??/??"})
and returns "General, # ?/? or # ??/??" in cell D3.
5. Calculate the intersection of two cell ranges
The CELL function is able to return the cell reference from a cell range based on two intersecting cell references.
Formula in cell B13:
The space character can be used as an intersect operator in Excel. The formula in cell B13 returns the top-left cell address of the intersecting cell references.
The image above shows how to calculate the address of two intersecting cell ranges using a VBA macro. The message box shows the address of the entire cell range.
5.1 VBA code
Sub TwoCellRanges()
MsgBox "Intersecting cell range: " & Application.Intersect(Range("B2:C7"), Range("C5:E10")).Address
End Sub
5.2 Where to put the code?
- Copy above VBA code.
- Press shortcut keys Alt + F11 to open the Visual Basic Editor.
- Press with mouse on "Insert" on the menu.
- Press with left mouse button on "Module".
- Paste code to window.
- Exit VB Editor and return to Excel.
'CELL' function examples
This article demonstrates how to work with multiple criteria using INDEX and MATCH functions. Table of Contents INDEX MATCH - […]
Functions in 'Information' category
The CELL function function is one of 19 functions in the 'Information' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form