FILTERXML Function
Pulls specific values out of XML text using an XPath.
Category
Web
Available in
Excel 2013+ for Windows, Microsoft 365 (not Mac or the web)
Returns
One value, or a list that spills down
Overview
FILTERXML reads XML — text made of tags like <order><item>Paper</item></order> — and returns the values an XPath points at. Several matches spill down a column.
It's often used with WEBSERVICE to read a web feed, and it's famous for a clever trick: splitting delimited text into a list, which works even in versions without TEXTSPLIT.
Syntax
Arguments
| Argument | Required? | What it does |
|---|---|---|
| xml | Yes | Well-formed XML text — from a cell, a WEBSERVICE call or a formula that builds it. |
| xpath | Yes | Which values to return, such as "//item" (every item), "//order/@id" (the id attribute), "//order[2]/item" or "//order[@region='North']/item". |
Examples
Example 1 — Split a comma-separated list
Turn the commas into tags so the text becomes XML, then ask for every <s> element.
| A | B | |
|---|---|---|
| 1 | Regions | List |
| 2 | North,South,East,West | North |
| 3 | South | |
| 4 | East | |
| 5 | West |
Use //s[2] for just the second item. In Microsoft 365, TEXTSPLIT does this more simply.
Example 2 — Read values from an XML export
A2 holds an order export: <orders><order id="1001" region="North"><item>Paper</item><qty>4</qty></order>…</orders>.
| A | B | C | |
|---|---|---|---|
| 1 | XML | North items | |
| 2 | <orders><order id="1001" region="North"><item>Paper</item><qty>4</qty></order><order id="1002" region="South"><item>Toner</item><qty>12</qty></order><order id="1003" region="North"><item>Pens</item><qty>30</qty></order></orders> | Paper | |
| 3 | Pens |
- //order/@id returns 1001, 1002 and 1003 as numbers.
- //order[qty>10]/item returns Toner and Pens.
- //order[last()]/item returns Pens.
Common mistakes
- #VALUE! The XML isn't valid (a stray & must be written &), the XPath is wrong, or nothing matched.
- JSON instead of XML. FILTERXML only reads XML. For JSON from a web service, use Power Query (Data › Get Data › From Web).
Notes
- XPath names are case-sensitive: //Item and //item are different.
- In our practice Sandbox, FILTERXML supports the common XPath forms: / and // paths, *, @attributes, text(), and filters such as [2], [last()], [@a='x'] and [qty>10].
- Related functions: WEBSERVICE, ENCODEURL, TEXTSPLIT, SUBSTITUTE.

Author
Kyle Stott
Certified Microsoft Excel Expert
Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.