This Excel tutorial explains how to use Cell Function to check Cell format, address and contents.
You may also want to read:
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.
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.
|
||||||||||||||||||||||||||||||||||||||||||||||
| “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.
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))
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.
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