Contents
Day 4 of the marathon 30 Excel functions in 30 days we got detailed information about the working environment using the function INFO (INFORM), such as Excel version and recalculation mode.
The eleventh day of the marathon we will devote to the study of the function CELL (CELL), which will report information about the formatting of the cell, its contents and location. It works similar to the function INFO (INFORM), i.e. has a list of values that can be entered into the function, but contains not one, but two arguments.
So let’s look at the information and examples by function CELL (CELL). If you have something to add to our examples and information, please share it in the comments.
Function 11: CELL
Function CELL (CELL) shows information about the formatting, content, and location of the cell at the given link.
How can the CELL function be used?
Function CELL (CELL) can report the following information about the cell:
- Numeric cell format.
- Sheet name.
- The alignment or width of the column.
CELL Syntax
Function CELL (CELL) has the following syntax:
CELL(info_type,reference)
ЯЧЕЙКА(тип_сведений;ссылка)
info_type (info_type) is one of the argument options:
- address (address) – reference to the first cell in the argument reference (link) in text format.
- with (column) – column number of the cell in the argument reference (link).
- сolor (color) – returns 1 if the cell formatting provides for changing the color for negative values; in all other cases, 0 (zero) is returned.
- contents (content) – The value of the top left cell in the link.
- filename (filename) – filename and full path.
- format (format) – number format of the cell.
- parentheses (brackets) – returns 1 if the cell is formatted to display positive or all numbers in parentheses; in all other cases returns 0 (zero).
- prefix (prefix) – text value corresponding to the cell label prefix (shows the type of alignment).
- protect (protection) – 0 = cell not locked, 1 = locked.
- row (string) is the row number of the cell.
- type (type) – the type of data in the cell (empty, text, other).
- width (width) – the width of the cell column.
Pitfalls of the CELL function
There are a few things to watch out for when using the function CELL (CELL):
- If the argument reference (reference) is omitted, the result is returned for the last modified cell. To be sure that the result is exactly what you need, it is advisable to always indicate the link. You can even refer to the cell that contains the function itself CELL (CELL).
- When working with the function CELL (CELL), sometimes it is necessary to recalculate the sheet to update the result that the function returns.
- If as an argument info_type (detail_type) value selected filename (filename) and the Excel workbook has not yet been saved, the result is an empty string.
Example 1: Cell Number Format
With meaning format (format) You can use the function CELL (CELL) to show the number format of the cell. For example, if cell B7 has the format General (General), then the result of the formula will be G:
=CELL("format",C2)
=ЯЧЕЙКА("формат";C2)
Example 2: Sheet Title
With meaning filename ( filename ) function CELL (CELL) will show the file path, file name and sheet name.
=CELL("filename",B2)
=ЯЧЕЙКА("имяфайла";B2)
You can extract the name of the sheet from the result obtained using other functions. In the formula below, using the functions MID (PSTR) and FIND (FIND), find the square brackets and return the 32 characters that follow them (the length of the sheet name is limited to 31 characters).
=MID(CELL("filename",C3),FIND("]",CELL("filename",C3))+1,32)
=ПСТР(ЯЧЕЙКА("имяфайла";C3);НАЙТИ("]";ЯЧЕЙКА("имяфайла";C3))+1;32)
Example 3: Substituting the info_type argument (info_type) from a dropdown list
Instead of entering an argument value info_type (detail_type) into a function CELL (CELL) as a text string, you can refer to a cell that contains valid values. In this example, cell B4 contains a drop-down list, and instead of an argument info_type (detail_type) is a reference to this cell. Argument reference (link) refers to cell B2.
When value is selected protect (protect): The result is 1 if the cell is locked, or 0 (zero) if it is not.
=CELL(B4,B2)
=ЯЧЕЙКА(B4;B2)
When value is selected width (width), the result shows the width of the column in integer format. The unit of measurement in this case is the width of one character at a standard font size.
