Purpose

Return value

Syntax

=FIELDVALUE(value,field_name)
  • value - The data type with field values.
  • field_name - The field name provided as a text value.

Using the FIELDVALUE function

The Excel FIELDVALUE function extracts a given field value from a Data Type . The field is specified by name and provided as a text value. Use the FIELDVALUE function to retrieve a field value by name from linked data types like Stocks, Geography, Food, Currency, and more.

Examples

To retrieve a field value from a linked data type, provide the field name as text in double quotes (""). For example, with a city in cell A1, linked to a Geography data type, you can request population data like this:

=FIELDVALUE(A1,"city population")

In the example shown, the formula in cell C5, copied down, is:

=FIELDVALUE(B5,"city population")

The result is population data for the 12 cities listed in B5 to B16.

Alternative syntax

The FIELDVALUE function is an alternative the “dot” syntax for retrieving a field value from a data type. The two formulas below return the same result:

=FIELDVALUE(B5,"area")
=B5.area

Note square brackets ([]) are required for field names that contain spaces:

=FIELDVALUE(B5,"city population")
=B5.[city population]

When the field name is a single word, the brackets are not required

Trapping errors

In column D of the example, FIELDVALUE is used to extract “Area” like this:

FIELDVALUE(B5,"area")

This returns a #FIELD! error for cities where area is not available. To trap this error and return an empty string ("") where there are errors, the IFERROR function is used in cell D5 like this:

=IFERROR(FIELDVALUE(B5,"area"),"")

As a result, the cells for Cairo, Beijing, Istanbul, and Mexico City display nothing instead of a #FIELD! error.

Purpose

Return value

Syntax

=FORMULATEXT(reference)
  • reference - Reference to cell or cell range.

Using the FORMULATEXT function

The FORMULATEXT function returns a formula as a text string from a cell reference. The FORMULATEXT can be used to extract a formula as text from a cell reference. The text returned by FORMULATEXT is the same as displayed in the formula bar when a cell with a formula is selected. Once text is extracted with FORMULA text, it can be handled as text in another formula.

FORMULATEXT takes just one argument, reference , which is normally a cell reference like A1. If you use FORMULATEXT on a cell that doesn’t contain a formula, it returns #N/A. FORMULATEXT will handle formulas up to 8192 characters.

In Excel 365 , the FORMULATEXT function will return more than one result when given a range that contains formulas. These results will spill like other dynamic array formulas. In earlier versions of Excel, FORMULATEXT will return a single result from the upper left cell in the range.

To check if a cell contains a formula, use the ISFORMULA function . To temporarily display all formula in a worksheet with the keyboard shortcut Control + ` .

Examples

Assuming the formula =TODAY() in cell A1:

=FORMULATEXT(A1) // returns "=TODAY()"

With the formula =C1+B1 in cell D1:

=FORMULATEXT(D1) // returns "=C1+B1"

With the text “apple” in cell F1:

=FORMULATEXT(F1) // returns #NA!

One quirk of FORMULATEXT is that it will not display a circular reference error if given a reference to the same cell it resides in. For example, if the formula below is entered in cell A1:

=FORMULATEXT(A1) // returns =FORMULATEXT(A1)

The result is simply “=FORMULATEXT(A1)”.

Notes

  • To test if a cell contains a formula or not, use the ISFORMULA function.
  • FORMULATEXT will return #N/A if a cell does not contain a formula.
  • FORMULATEXT will return #N/A when referencing another workbook that is not open.
  • FORMULATEXT was introduced in Excel 2013.