FILTERXML function
specific data from the XML content by using the specified XPathThis function is not available in Excel for the web.
Web · Excel 2013+ · Guided Excel example
Where can you use it?
Teams bringing external data into Excel. Check the connection and Excel platform required by the example when a report depends on a URL or other external data.
Scenario in the workbook
Select a quantity from XML using XPath. The document must be valid XML and contain the requested element.
A worked example from the workbook
| Row | A |
|---|---|
| 6 | Input |
| 7 | <r><qty>2</qty></r> |
| 8 | <r><qty>6</qty></r> |
| 9 | <r><qty>9</qty></r> |
=FILTERXML(A7,"//qty")Illustrative output2
See the inputs and instructions for all 3 examplesCopyable formulas
=FILTERXML("<items><item>42</item></items>","//item")=FILTERXML("<items><item>42</item></items>";"//item")Expected result
42
Worked examples
Select a quantity from XML using XPath. The document must be valid XML and contain the requested element.
Column F provides expressions as text. Satisfy the stated requirements, then paste into an empty Excel cell and compare with column G. The result was not verified in desktop Excel here.
| Row | A |
|---|---|
| 6 | Input |
| 7 | <r><qty>2</qty></r> |
| 8 | <r><qty>6</qty></r> |
| 9 | <r><qty>9</qty></r> |
| Trial | Expression | Sample output |
|---|---|---|
| Record 1 | =FILTERXML(A7,"//qty") |
2 |
| Record 2 | =FILTERXML(A8,"//qty") |
6 |
| Record 3 | =FILTERXML(A9,"//qty") |
9 |
If the result is missing
Use desktop Excel for Windows. The XPath must select an element in valid XML.