📎 This article includes 1 downloadable practice file ↓
In this article
FILTERXML extracts values from XML with an XPath. Its famous side use: splitting text in versions without TEXTSPLIT.
Syntax
=FILTERXML(xml, xpath)| Argument | What it means |
|---|---|
xpath |
e.g. “//item/price”. |
Examples
Example 1
=FILTERXML("<t><s>"&SUBSTITUTE(A2,",","</s><s>")&"</s></t>", "//s")Split a comma list into cells in Excel 2016/2019.
FILTERXML in practice
Where you will use it
- Split text by a delimiter in older Excel
- Read values from an XML API response
- Extract the nth word
Worked example
| Text | Item | Formula | Result |
|---|---|---|---|
| Pune,Delhi,Goa | 2 | =FILTERXML("<t><s>"&SUBSTITUTE(A2,",","</s><s>")&"</s></t>","//s["&B2&"]") | Delhi |
| red,green,blue | 3 | =FILTERXML("<t><s>"&SUBSTITUTE(A3,",","</s><s>")&"</s></t>","//s["&B3&"]") | blue |
| a,b | 1 | =FILTERXML("<t><s>"&SUBSTITUTE(A4,",","</s><s>")&"</s></t>","//s["&B4&"]") | a |
Results calculated in Excel.
Mistakes people make
| Mistake | What to do instead |
|---|---|
| Special characters | &, < and > break the XML; SUBSTITUTE them first |
| Mac and web | FILTERXML is Windows-only |
Related functions
📚 Part of the free Excel course: Beginner → Expert · Try it in the Formula Lab or ask the AI Helper.
⚡ Interactive Practice Lab & Formula Tester:
Test formulas dynamically in our web formula laboratory or download the companion Excel workbook.
📎 Practice files for this article
- 📗Practice workbook: Text & cleaning24 tasks on realistic sales data — type your formula, the Check column turns green when it is right. Answers sheet included.⬇ XLSX · 35 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
✨ Ask AI about this article
Stuck on a step? Ask a question and the AI answers using this article.
Free · AI can be wrong