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

Want to practice FILTERXML in a real spreadsheet? Open a blank sheet in our Practice Sandbox and try it with live data, with the AI Excel tutor beside you. No Excel needed — just a free account.Try it free in the Sandbox

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

=FILTERXML(xml, xpath) Explain

Arguments

ArgumentRequired?What it does
xmlYesWell-formed XML text — from a cell, a WEBSERVICE call or a formula that builds it.
xpathYesWhich 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.

B2fx=FILTERXML("<t><s>"&SUBSTITUTE(A2,",","</s><s>")&"</s></t>","//s")
AB
1RegionsList
2North,South,East,WestNorth
3South
4East
5West
=FILTERXML("<t><s>"&SUBSTITUTE(A2,",","</s><s>")&"</s></t>","//s") Explain

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>.

B2fx=FILTERXML(A2,"//order[@region='North']/item")
ABC
1XMLNorth 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
3Pens
=FILTERXML(A2,"//order[@region='North']/item") Explain
  1. //order/@id returns 1001, 1002 and 1003 as numbers.
  2. //order[qty>10]/item returns Toner and Pens.
  3. //order[last()]/item returns Pens.

Common mistakes

  • #VALUE! The XML isn't valid (a stray & must be written &amp;), 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.
Kyle Stott

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.