FILTERXML

Extract specific values from XML content using an XPath query with FILTERXML.

Web
|
Excel 2013+
|
Google Sheets Not supported

Syntax

=FILTERXML(xml, xpath) Returns: Varies

Arguments

Argument Required Description
xml Yes A string of valid XML from which you want to extract data.
xpath Yes An XPath query string that specifies which nodes or attributes to return from the XML.

About

FILTERXML returns data from XML content based on an XPath expression you provide. The first argument is the XML string, and the second is an XPath query that tells Excel which nodes or values to pull out. This is helpful when you receive data in XML format and need to grab specific pieces without manual parsing.

A common workflow pairs FILTERXML with the WEBSERVICE function. WEBSERVICE fetches XML from a URL, and FILTERXML picks out the values you need. You can also use it on XML stored in cells, for example when importing data from APIs or configuration files.

FILTERXML is only available on Windows versions of Excel (2013 and later). It is not supported in Excel for Mac, Excel Online, or Google Sheets. If you need to split text in environments where FILTERXML is not available, consider using a combination of MID and FIND.

Examples

Extracting a book title from XML

=FILTERXML("<catalog><book><title>Budget Planning</title><author>J. Smith</author></book></catalog>","//book/title")
Pulls the text "Budget Planning" from the <title> node inside the XML. The XPath "//book/title" tells Excel to find any <book> element and return its <title> child.

Splitting a delimited string into rows

=FILTERXML("<s><i>" & SUBSTITUTE(A1,",","</i><i>") & "</i></s>","//i")
A popular trick that turns a comma-separated list (like "Red,Blue,Green") into separate values by wrapping each item in XML tags. The result spills into multiple cells, one value per row.

Getting a specific item by position

=FILTERXML("<items><v>North</v><v>South</v><v>East</v><v>West</v></items>","//v[3]")
Returns "East", the third <v> element. XPath positions start at 1, so [3] grabs the third node. This is useful when you know the order of values in the XML.

Watch out for

⚠

Not available on Mac or Excel Online

You try to use FILTERXML on a Mac or in Excel for the web and get a #NAME? error.

→ FILTERXML only works in desktop Excel for Windows (2013 and later). For cross-platform alternatives, try MID and FIND combinations.

⚠

Invalid XML causes #VALUE! error

The XML string is malformed (missing closing tags, unescaped characters, etc.) and FILTERXML returns #VALUE!.

→ Make sure your XML is well-formed. Every opening tag needs a matching closing tag, and special characters like & must be written as & inside XML.

⚠

XPath returns nothing (#VALUE! error)

Your XPath expression does not match any nodes in the XML, so FILTERXML returns an error.

→ Double-check that element names in your XPath match the XML exactly (XPath is case-sensitive). Test with a simpler XPath like "//" followed by the element name to confirm the structure.

Tips & notes

FILTERXML is often used as a creative workaround for splitting delimited text. Wrap the text in XML tags using SUBSTITUTE, then use FILTERXML with an XPath to extract each piece.

Common questions

Does FILTERXML work in Google Sheets?

No. FILTERXML is an Excel-only function available on Windows desktop (2013 and later). Google Sheets has its own IMPORTXML function for fetching XML from URLs, but it works differently.

Can FILTERXML return multiple values at once?

Yes. If your XPath matches more than one node, FILTERXML returns an array of values that spills into adjacent cells (in Excel 365) or can be used inside functions like INDEX to pick a specific result.

What is XPath?

XPath is a query language for selecting parts of an XML document. In FILTERXML, you write an XPath expression like "//book/title" to tell Excel exactly which nodes to extract from the XML string.