Author: Oscar Cronquist Article last updated on July 08, 2021

How to use the FILTERXML function

The FILTERXML function extracts specific values from XML content by using the given xpath. The function was introduced in 2013 and you need Excel 2013 or a newer version to use the FILTERXML function.

Formula in cell B3:

=FILTERXML(B3,"//title")

Excel Function Syntax

FILTERXML(xml, xpath)

Arguments

xml Required. A value in XML format.
xpath Required. A value in XPath format.

Comments

  • The FILTERXML returns a #VALUE! error if the XML is not valid.
  • The FILTERXML returns a #VALUE! error if the XML contains a namespace with a prefix that is invalid.

1. How to extract XML attribute?

How to use the FILTERXML function extract attribute

An XML element may contain an attribute, the following formula demonstrates how to extract a given attribute from XML data.

Formula in cell B6:

=FILTERXML(B3,"//NorthAmerica/@city")

Back to top

2. How to import XML data?

How to use the FILTERXML function import xml

  1. Select a destination cell.
  2. Go to tab "Developer" on the ribbon.
  3. Press with left mouse button on the "Import" button, a file dialog box appears.
  4. Press with mouse on an XML file to select it.
  5. Press with left mouse button on the "Open" button to import the file.

Here is what the example XML data looks like:

How to use the FILTERXML function xml example

Back to top

3. Put XML data in different columns based on XML tags?

How to use the FILTERXML function columns

Formula in cell B6:

=FILTERXML($B$3,"//"&B$5)

Explaining formula in cell B6

Step 1 - Concatenate column header to build xpath arg

"//"&B$5

becomes

"//"&"Europe"

and returns "//Europe"

Step 2 - Extract XML data

FILTERXML($B$3,"//"&B$5)

becomes

FILTERXML($B$3, "//Europe")

becomes

FILTERXML("<countries><Europe>France</Europe><Asia>China</Asia><Europe>Spain</Europe><Asia>Japan</Asia><Asia>Thailand</Asia><Africa>Kenya</Africa></countries>", "//Europe")

and returns {"France";"Spain"}.

Back to top

Get the Excel file


FILTERXML-function.xlsx