FILTERXML function

The FILTERXML function returns specific data from XML content by using the specified xpath.

Notes: 

  • The FILTERXML function is not available in Excel for the web and Excel for Mac.

  • This function may appear in the function gallery in Excel for Mac, but it relies on features of the Windows operating system, so it will not return results on Mac.

Syntax

FILTERXML(xml, xpath)

The FILTERXML function syntax has the following arguments.

  • xml    Required. A string in valid XML format.

  • xpath    Required. A string in standard XPath format.

Remarks

  • If xml is not valid, FILTERXML returns the #VALUE! error value.

  • If xml contains a namespace with a prefix that is not valid, FILTERXML returns the #VALUE! error value.

Example

This example uses the FILTERXML function on XML data returned in cell B2, which is provided by the results of the WEBSERVICE function on the web query in cell B1.

An example of the FILTERXML function

The data from FILTERXML is returned in cells B3:B5 and C3:C5, and shows the three most recently updated query results on Wikipedia and the time of their updates (in UTC, "Coordinated Universal Time").

Cells B3:B5 contain the formula =FILTERXML(B3,"//rc/@title"), which needs to be entered as an array formula in those cells. Select B3:C5, enter the formula, and press Ctrl+Shift+Enter. Cells C3:C5 contain the formula =FILTERXML(B3,"//rc/@timestamp"), and also needs to be entered as an array formula.

Note: If you have a current version of Office 365, then you can simply enter the formula in the top-left-cell of the output range, then press ENTER to confirm the formula as a dynamic array formula. Otherwise, the formula must be entered as a legacy array formula by first selecting the output range, entering the formula in the top-left-cell of the output range, and then pressing CTRL+SHIFT+ENTER to confirm it. Excel inserts curly brackets at the beginning and end of the formula for you. For more information on array formulas, see Guidelines and examples of array formulas.

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

Expand your Office skills
Explore training
Get new features first
Join Office Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×