The Excel Cell function
returns information about a cell in a worksheet. The type of information to be
returned is specified as info_type. CELL can get things like address
and filename, as well as detailed info about the formatting used in the
cell.
Syntax
The syntax for the CELL
function in Microsoft Excel is:
=CELL( type, [range] )
Parameters or Arguments
type
The type of information that
you'd like to retrieve for the cell. type can be one of the following values:
|
Value
|
Explanation
|
|
"address"
|
Address of the cell. If the cell refers to a range, it is the first
cell in the range.
|
|
"col"
|
Column number of the cell.
|
|
"color"
|
Returns 1 if the color is a negative value; Otherwise it returns 0.
|
|
"contents"
|
Contents of the upper-left cell.
|
|
"filename"
|
Filename of the file that contains reference.
|
|
"format"
|
Number format of the cell. See example formats below.
|
|
"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.
|
"format"
For the "format"
value, described above, the values returned are as follows:
|
Returned Value for "format"
|
Explanation
|
|
"G"
|
General
|
|
"F0"
|
0
|
|
",0"
|
#,##0
|
|
"F2"
|
0
|
|
",2"
|
#,##0.00
|
|
"C0"
|
$#,##0_);($#,##0)
|
|
"C0-"
|
$#,##0_);[Red]($#,##0)
|
|
"C2"
|
$#,##0.00_);($#,##0.00)
|
|
"C2-"
|
$#,##0.00_);[Red]($#,##0.00)
|
|
"P0"
|
0%
|
|
"P2"
|
0.00%
|
|
"S2"
|
0.00E+00
|
|
"G"
|
# ?/? or # ??/??
|
|
"D4"
|
m/d/yy or m/d/yy h:mm or mm/dd/yy
|
|
"D1"
|
d-mmm-yy or dd-mmm-yy
|
|
"D2"
|
d-mmm or dd-mmm
|
|
"D3"
|
mmm-yy
|
|
"D5"
|
mm/dd
|
|
"D6"
|
h:mm:ss AM/PM
|
|
"D7"
|
h:mm AM/PM
|
|
"D8"
|
h:mm:ss
|
|
"D9"
|
h:mm
|
range
Optional. It is the cell (or
range) that you wish to retrieve information for. If the range parameter is
omitted, the CELL function will assume that you are retrieving information for
the last cell that was changed.
Examples of the Excel Cell Function
In the following example
spreadsheet, the Excel Cell function is used to return different properties of
the cell A1. It should be noted that, in the spreadsheet, cell A1 is formatted
with the Custom Format #,##0; [Red] -
#,##0.
Formulas:
|
|
A
|
B
|
|
1
|
- 9,999
|
=CELL(
"address", A1 )
|
|
2
|
|
=CELL(
"col", A1 )
|
|
3
|
|
=CELL(
"color", A1 )
|
|
4
|
|
=CELL(
"contents", A1 )
|
|
5
|
|
=CELL(
"filename", A1 )
|
|
6
|
|
=CELL(
"format", A1 )
|
|
7
|
|
=CELL(
"parentheses", A1 )
|
|
8
|
|
=CELL(
"prefix", A1 )
|
|
9
|
|
=CELL(
"protect", A1 )
|
|
10
|
|
=CELL(
"row", A1 )
|
|
11
|
|
=CELL(
"type", A1 )
|
|
12
|
|
=CELL(
"width", A1 )
|
Results:
|
|
A
|
B
|
|
1
|
- 9,999
|
$A$1
|
|
2
|
|
1
|
|
3
|
|
1
|
|
4
|
|
-9999
|
|
5
|
|
C:\[Book1.xlsx]Sheet1
|
|
6
|
|
,0-
|
|
7
|
|
0
|
|
8
|
|
|
|
9
|
|
1
|
|
10
|
|
1
|
|
11
|
|
v
|
|
12
|
|
8
|
Note that, in cell B6 of the above example spreadsheet, the
"format" type is returned as ",0-". This is comprised of
two parts: the ",0" part, which represents the #,##0 number format,
and the "-" part, which indicates that the cell is formatted in color
for negative values.
Note:
If the formatting of cell A1
in the above spreadsheet is changed, the formulas in cells B1-B12 will not
immediately recalculate. You can force them to recalculate by pressing the F9
key.
References
follow us on http://arivilm.blogspot.in
like us on https://www.facebook.com/Arivilm2501
No comments:
Post a Comment