ExcelFlux

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

RowA
6Input
7<r><qty>2</qty></r>
8<r><qty>6</qty></r>
9<r><qty>9</qty></r>
Record 1
=FILTERXML(A7,"//qty")

Illustrative output2

See the inputs and instructions for all 3 examples

Copyable formulas

English Excel · en-US
=FILTERXML("<items><item>42</item></items>","//item")
Turkish Excel · tr-TR
=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.

Microsoft Excel reference