Excel Cell Function to check Cell format, address, contents

This Excel tutorial explains how to use Cell Function to check Cell format, address and contents.

You may also want to read:

Excel ColorIndex Property

Excel verify Number format and convert Text to Number

Excel Cell Function to check Cell format, address, contents

Excel Cell Function is a very powerful Function that tells various information of a Cell. Cell Function has a list of arguments regarding Cell information.

excel_cell_function

Syntax of Cell Function

CELL(info_type, [reference])
[reference] is the Cell you want to get information from, only one Cell is expected. If you enter multiple Range, only information of the top left Cell will return.

info_type Explanation
“address” Return Absolute Address of a Cell
“col” Return column number of a Cell
“color” Returns 1 if the Cell is formatted to change color font for negative value; Otherwise it returns 0
“contents” Return Value of a Cell
“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” Return Number format of the cell. You are required to recalculate the formula (double click the formula Cell and then exit edit) when the format is changed.

If the Excel format is The CELL function 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”
“parentheses” Returns 1 if the cell is formatted with parentheses; Otherwise, it returns 0.
“prefix” Label prefix for the cell.
* Returns a single quote (‘) if the cell is left-aligned.
* Returns a double quote (“) if the cell is right-aligned.
* Returns a caret (^) if the cell is center-aligned.
* Returns a back slash (\) if the cell is fill-aligned.
* Returns an empty text value for all others.
“protect” Returns 1 if the cell is locked. Returns 0 if the cell is not locked.
“row” Row number of the cell.
“type” Returns “b” if the cell is empty.
Returns “l” if the cell contains a text constant.
Returns “v” for all others.
“width” Column width of the cell, rounded to the nearest integer.

Example of Cell Function

Some of the info_type are self-explanatory, and more importantly they are rarely used, so I am not going to go through each of them.

The most important use of Cell Function includes Address, Row and Column. See the examples below.

excel_cell_function_02

You may use Substitute Function to get rid of the $ in absolute address.

=Substitute(CELL("address",A2),"$","")

The second most important use of Cell Function is to check whether a Cell contains a Date.

Since a Date is a serial number, from Excel’s perspective, Date is regarded as number. The only difference between a Date and Number is how it is formatted. Therefore to tell the difference between a Date and a Number, we should use info_type “format” and see if the returned value starts with “D”.

The following formula returns TRUE if it is a Date

=IF(ISNUMBER(A2),IF(LEFT(CELL("Format",A2),1)="D",TRUE,FALSE))

convert_text_to_number_04

Misuse of Cell Function

One important point to note is that the info_type “color” is not to detect color of the Cell, it is just a checking whether you have formatted to number to change color for negative number.

excel_cell_function_03

If you want to check the color of the Cell, use ColorIndex Property

Outbound References

https://support.office.com/en-ca/article/CELL-function-51bd39a5-f338-4dbe-a33f-955d67c2b2cf

 

Leave a Reply

Your email address will not be published. Required fields are marked *