Skip to main content

Excel FILTERXML Function

The FILTERXML Function returns specific values from XML text by using the given XPath.

filterxml-function 1


Syntax

=FILTERXML(xml,xpath)


Arguments

  • Xml (required): A string in valid XML format.
  • Xpath (required): A string in valid XPath format.

Return Value

The FILTERXML function returns specific values from XML text.


Function notes

  1. The FILTERXML function is introduced in Excel 2013. Therefore, it is unavailable in earlier Excel versions. And it is not available in Excel for the web or Excel for Mac, either.
  2. The FILTERXML function may appear in Excel for Mac's library, but it doesn’t return results on Mac because it relies on Windows operating system functionality.
  3. The #VALUE! error value occurs if one of the situations occurs:
    • the supplied xml argument is not valid;
    • the supplied xml argument contains a namespace with a prefix that is not valid.

Example

As the below screenshot shows, cell B4 contains the XML text. To get the specific data from this XML text using the specified XPath, please do as follows.

Please copy the formula below into cell D4, then press the Ctrl + Shift + Enter keys to get the result.

=FILTERXML(B4,"//food/name")

filterxml-function 2

Notes:

  1. n the above formula, //food/name is the input xpath argument.
  2. In Excel for Microsoft 365, you can directly press the Enter key after you input the formula to get the result because Excel 365 supports the dynamic arrays feature that spills the results automatically.

Relative Functions:

  • Excel EVEN Function
    The EVEN function rounds numbers away from zero to the nearest even integer.

  • Excel EXP Function
    The EXP function returns the result of the constant e raised to the nth power.

 

Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations