# 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

The following article has a formula that contains the CELL function.

This article demonstrates formulas that return the last value in a given cell range or column. The image above shows […]

## Functions in this article

### Functions in 'Information' category

The CELL function function is one of many 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 signsUse 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 OscarYou can contact me through this contact form