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:

Excel Function Syntax

FILTERXML(xml, xpath)

Arguments

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?

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

Formula in cell B6:

Back to top

2. How to import XML data?

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:

Back to top

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

Formula in cell B6:

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