SheetSkill

FILTERXML function

Pulls values out of XML text using an XPath, usually after WEBSERVICE downloads it.

Works in desktop Excel only

Syntax

=FILTERXML(xml, xpath)

How to use FILTERXML in Excel, step by step

FILTERXML doesn’t run in a web practice sheet because it reads XML, usually downloaded with WEBSERVICE. Follow these steps in Excel itself.

You’ll need

  • Excel for Windows (not available in Excel for the web or Mac)
  1. Get some XML. Either from WEBSERVICE, or type it yourself: in A1 enter <r><n>5</n><n>7</n></r>.
  2. Write the XPath. //n means “every n element anywhere”. //item/title means “the title inside every item”.
  3. Use it. Type =FILTERXML(A1,"//n"). The results spill down: 5 and 7.
  4. Split text with it (handy trick). To split "a,b,c" into rows: =FILTERXML("<t><s>"&SUBSTITUTE(A3,",","</s><s>")&"</s></t>","//s"). In Excel 365 TEXTSPLIT does this more simply.

Example

=FILTERXML("<r><n>5</n><n>7</n></r>","//n")

Result: 5 and 7 (spilled)

XPath is case-sensitive.
Text that isn’t valid XML gives #VALUE!.