The Excel FILTERXML function returns specific data from XML text using a specified XPath expression.
XML is a text format for storing and transporting data. It is not dependent on any particular hardware or software. XML is extensible and is designed to transport data, as opposed to displaying data in a particular way. XML has strict syntax rules which allows software to traverse the structure of an XML document and perform various operations.
XPath is a special query language for selecting the elements and attributes in an XML document. The FILTERXML function uses XPath to match and extract data from text in XML format.
Note: FILTERXML is not available in Excel on the Mac, or in Excel Online.
In the example shown, the cell contains XML that carries information about albums published as CDs. Each CD contains the title of the album, the name of the artist, and the year the album was released. The formula in cell D5 uses FILTERXML to extract all titles:
The xml argument is the XML in cell B5, and the xpath argument is the expression "//cd/title", which matches all title elements with the parent . In Excel 365, which supports dynamic arrays, the results spill into the range D5:D14 automatically.
Excel does not have a function dedicated to splitting text to an array, similar to the PHP explode function, or Python split method. As a workaround, you can use the FILTERXML function, after first adding XML markup to...
Excel does not provide a dedicated function to split a text string to an array, like PHP's explode(), or Python's split(). However, you can use the LAMBDA function to create a custom function that works in a similar way...
The FILTERXML function can parse XML using XPath expressions. XML is a special text format designed transport data, with features that allow it to be easily parsed and verified by software. XPath is a query language for...