MS Excel: Cell Function

In Excel, the Cell function can be used to retrieve information about a cell. This can include contents, formatting, size, etc.

The syntax for the Cell function is:

Cell( type, range )

type is 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.

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.00

",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 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.

Applies To:

  • Excel 2007, Excel 2003, Excel XP, Excel 2000

For example:

Let’s take a look at an example:

Based on the Excel spreadsheet above:

=Cell("col", A1)
would return 1

=Cell("address", A2)
would return $A$2

=Cell("format", A2)
would return P2


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: