How to Use the FILTERXML Function in Excel

Introduction

In this article, we explore the FILTERXML function in Microsoft Excel and Google Sheets. FILTERXML is a robust tool designed to extract specific data from XML strings. We’ll cover the function’s syntax, illustrate its practical applications, and demonstrate its usage through various examples.

Syntax

The syntax for the FILTERXML function in both Excel and Google Sheets is as follows:

=FILTERXML(xml, xpath)

The parameters are defined as:

• xml: The XML string you wish to parse.
• xpath: The XPath expression specifying the piece of data to extract from the XML string.

Task Examples

FILTERXML can be particularly useful for:

1. Extracting specific elements from XML strings.
2. Summarizing data from complex XML structures.
3. Filtering and analyzing XML data directly in a spreadsheet.

How to Use

Let’s examine a practical implementation of the FILTERXML function in Excel and Google Sheets.

Example

Consider you have an XML string in cell A1:

<employees> <employee> <name>Alice</name> <department>Sales</department> <salary>50000</salary> </employee> <employee> <name>Bob</name> <department>Marketing</department> <salary>60000</salary> </employee> </employees>

To extract the names of employees, use this FILTERXML formula:

=FILTERXML(A1, "//name")

This formula extracts an array of names from the XML data.

If you need to retrieve employee salaries, apply the following formula:

=FILTERXML(A1, "//salary")

This returns an array of salaries from the XML content.

By utilizing different XPath expressions with the FILTERXML function, you can tailor data extraction to meet specific needs within your spreadsheet.

Conclusion

The FILTERXML function is an invaluable asset for handling XML data within Excel and Google Sheets, allowing for the extraction and manipulation of detailed information. This overview sheds light on the function’s syntax and demonstrates its versatility through practical examples. Utilize FILTERXML in your projects to fully leverage its capabilities!

More information: https://support.microsoft.com/en-us/office/filterxml-function-4df72efc-11ec-4951-86f5-c1374812f5b7