Usage of Google Sheets CELL Function and Examples

0
63
Usage of Google Sheets CELL Function and Examples

Google Sheets CELL function is related to gathering limited information about any Cell in a Spreadsheet. But don’t undermine this function as it can be a useful tool on certain occasions. In this new Google Sheets tutorial we can learn what are those information Google Sheets CELL function can return.

To use CELL Function in Google Sheets, first you should know about information type called “info_type”.

How to USE CELL Function in Google Sheets

I’ve already mentioned about the purpose of CELL function above. Now see the syntax.

CELL(info_type, reference)

In this syntax, “reference” is the cell reference of which you want to get the information. Now see detailed information about “info_type” after one example.

=CELL(“COL”,G2)

This Google Sheets CELL formula would return the result 7 which is the Column Number of G. Here “COL” is the “info_type” to return Column number. You can see below all the available info types in CELL function.

Google Sheets CELL Function “Info Types” and Examples

I’ve detailed available info types in different sections like Part I, Part II etc. So I hope, you can easily grasp the usage of this formula.

Part I

Formula: =CELL(“address”,A1)
Result: $A$1
Note: returns an absolute reference in text form.

Formula: CELL(“col”,A1)
Result: 1
Returns the column number of A1

Formula: CELL(“row”,A1)
Result: 1
Returns the row number of A1

Part II

Formula: CELL(“contents”,A1)
Returns the cell content in A1 as it is. If cell A1 contains the value 10, the formula would return the value 10. That means the formula is equal to =A1.

Part III

Formula: CELL(“type”,A1)
Result: It returns the letter B for Blank, L for Label or Plain Text and V for Value.

Example to Cell function where info_type is “type”.

Suppose the cell value in A1 is the text “Info Inspired”. The function would return the letter “L”. This you can use in an IF logical test as below.

Google Sheets CELL Formula in Logical Test.

=if(cell(“type”,A1)=“V”,A1*2)

This IF formula would check the cell A1 and if A1 contains number, it would multiply the number with 2. The following formula using ISNUMBER is equal to the above.

=if(ISNUMBER(A1),A1*2)

In both cases, if the value in A1 is text, the formula would return FALSE. But I suggest you to use the second formula as you can use this in an array hassle free. When you use the first formula with array, it would return “#VALUE!” error if any cell contains text value.

Cell formula in IF logical test in Google Sheets

Part IV

Formula: CELL(“prefix”,A1)

Here you can see that I’ve used “prefix” which is another CELL formula info_type. It merely returns the horizontal cell alignment info. It returns a single quote for left aligned text, double quotes for right aligned text, carat for centered text and empty for everything else.

get text alignment info in Google Sheets

Part V

Formula: CELL(“width”,A1)

By using width information type in Cell formula, you can find how many characters can perfectly fit in to a cell.

Conclusion

The above are the currently available info_type in Google Sheets CELL function. You can see that sometimes the results wrongly appear when using this function. If any of the above info type may return blank or wrong output, you can double click on the cell where you applied the formula to see the actual cell info. That’s all. Enjoy.

LEAVE A REPLY

Please enter your comment!
Please enter your name here