Excel like a finance pro.
Web function
FILTERXML
Returns data from XML text using an XPath query.
When to use it
Runs an XPath query on XML text and returns the matching values. Also the engine behind a well-known trick for splitting text in older Excel.
The shape of it
- Syntax
=FILTERXML(xml, xpath)
Worked examples
One value
=FILTERXML(A1,"//rate") → 0.92
The rate element from XML in A1.
Several values
=FILTERXML("<r><a>1</a><a>2</a></r>","//a") → {1;2}
Every a element, as an array.
Split text
=FILTERXML("<t><s>"&SUBSTITUTE("a,b,c",",","</s><s>")&"</s></t>","//s") → {a;b;c}
Wrap a delimited string in XML tags and split it, for versions without TEXTSPLIT.
Worth knowing
- XPath basics: //tag for any depth, /a/b for a path, [n] for the nth, @attr for attributes.
- JSON responses need to be converted or handled in Power Query.
- Results spill in Microsoft 365.
Where it goes wrong
- #VALUE! for malformed XML or an invalid XPath.
- Not available on Mac or Excel Online.
Related
Learn the moves here — or let Wauvel run them on your numbers.
Meet your AI CFO →One CFO-grade Excel tip a week
A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.