Practice free

FILTERXML function

FILTERXML pulls values out of XML text using an XPath, usually the response from WEBSERVICE, and spills multiple matches. It works in Windows Excel only.

Example

Read it as a sentence: read the XML text in AA1 and pull out every a element, which starts with 1.

Practice this in the app

Syntax

FILTERXML(xml, xpath)

xml (required)
Valid XML text.
xpath (required)
XPath expression pointing at the data you want.

Returns: The data found at the XPath; several matches spill.

One FILTERXML formula can fill more than one cell. When the result has several values, they spill into the cells below or to the right of the formula.

What Excel actually returns

Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.

FormulaExcel showsCase
=FILTERXML("<r><v>x</v></r>","//v") x Inline xml
=ISNUMBER(FILTERXML("<r><v>007</v></r>","//v")) TRUE Numeric text returned as number
=FILTERXML("<t><s>"&SUBSTITUTE("a,b,c",",","</s><s>")&"</s></t>","//s") a Split text trick
=FILTERXML("<r xmlns=""u""><v>1</v></r>","//v") 1 Namespace
=FILTERXML(Data!AA1,"//a") 1 All <a>
=FILTERXML(Data!AA1,"//b") t Single node
=FILTERXML("","//a") #VALUE! Empty xml
=FILTERXML(Data!AA1,"count(//a)") #VALUE! Count()

The Data sheet these formulas read

A cell that starts with = holds that formula.

Watch out for

  • Malformed XML or XPath returns #VALUE!.
  • XML namespaces need extra care in the XPath.

Where it works

Excel
FILTERXML arrived in Excel 2013. Older versions do not have it. Windows only; not in Excel for Mac or the web.
Google Sheets
No direct equivalent: `IMPORTXML`. Sheets IMPORTXML fetches and parses a URL; there is no direct text-only counterpart.

Part of Web functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .