FILTERXML Formula in Excel

The FILTERXML formula in Excel returns specific data from XML content based on the specified XPath. The FILTERXML formula in Excel is a part of web formulas.

The FILTERXML formula is available in Excel 2013 and all subsequent versions on Windows. The FILTERXML formula is not available in Excel for Mac and web (online). Even though you may find the FILTERXML in Mac under formulas, still it will not return the result as it relies on Windows operating system.

How to use FILTERXML Formula in Excel?

The article in this webpage describes how to use the FILTERXML formula in Excel with syntax, detailed explanation, and examples.

FILTERXML Syntax

=FILTERXML(xml, xpath)

The arguments of FILTERXML formula are:

• XML (required argument) – This is the string in valid XML format. The XML (eXtensible Markup Language) is a file format for storing, transmitting, and reconstructing arbitrary data.
• XPath (required argument) – This is the string in standard XPath format. The XPath (XML Path Language) is used to identify parts of an XML data.

FILTTERXML Examples

Example 1

Let’s understand the FILTERXML formula in Excel with example.

In the below example of FILTERXML formula in cell C9, the 4th word is FILTERXML from the XML data in cell C7 (sentence in cell C6).

=FILTERXML(C7,”/Rath/Point[4]”)

Example 2

Let’s understand the FILTERXML in Excel with another example.

In case you are selecting the XPath from a cell then you do not have to add the double quote. In this FILTERXML example, the 5th word is “formula” from the XML data in cell C7 (sentence in cell C6).

=FILTERXML(C7,”/Rath/Point[5]”)

OR

=FILTERXML(C7,C8)

How to Convert Text to XML in Excel?

To convert the text to XML data in Excel, follow the below steps:

• “<Rath><Point>” – you can define any text as per your need instead of Rath and Point. These should be in double quotes.
• &SUBSTITUTE(C6,” “,”</Point><Point>”) – use the SUBSTITUTE formula to replace the blank space with “<Rath><Point>”.
• &”</Point></Rath>” – close the text which you have started in the first step with “/” sign. The second should be closed then the first.

To convert the sentence to XML data in Excel, the formula in cell C7 is:

=”<Rath><Point>”
&SUBSTITUTE(C6,” “,”</Point><Point>”)
&“</Point></Rath>”

How to Import XML data in Excel?

There are two ways you can import the XML data into Excel and those are:

1. Go to Data Tab:
   • ⇒ Get Data (under Get & Transform Data),
   • ⇒ From File,
   • ⇒ and then click on From XML to import the XML data.
2. Go to Developer Tab and click on Import under XML section to import the XML data.

Important Notes

• The FILTERXML formula is available in Excel 2013 and next versions in Windows.
• The FILTERXML formula is not available in Excel for Mac or web users.
• The FILTERXML formula will not work and return as #VALUE! error if:
  • the XML is not valid, or
  • the XML contains invalid namespace with prefix.