Skip to main content

FILTERXML function

The FILTERXML function returns specific data from XML content by using the specified xpath.

  • The FILTERXML function is not available in Excel for the web and Excel for Mac.
  • This function may appear in the function gallery in Excel for Mac, but it relies on features of the Windows operating system, so it will not return results on Mac.


FILTERXML(xml, xpath)

The FILTERXML function syntax has the following arguments.

Argument Description
xml Required. A string in valid XML format
xpath Required. A string in standard XPath format


This example uses the FILTERXML function on XML data returned in cell B2, which is provided by the results of the WEBSERVICE function on the web query in cell B1.

The data from FILTERXML is returned in cells B3:B12 and C3:C12, and shows the most recently updated query results on Wikipedia and the time of their updates (in UTC, "Coordinated Universal Time").

Cell B1 contain the query url string:

Cell B2 contain the formula:


Cell B2 returns:

<?xml version="1.0"?>
<api batchcomplete="">
	<continue rccontinue="20221015085902|1559952690" continue="-||"/>
			<rc type="edit" ns="0" title="2022 NASCAR Cup Series" pageid="64612305" revid="1116192151" old_revid="1115951681" rcid="1559952705" timestamp="2022-10-15T08:59:07Z"/>
			<rc type="edit" ns="0" title="Bothrocara" pageid="5278803" revid="1116192149" old_revid="1017742971" rcid="1559952703" timestamp="2022-10-15T08:59:06Z"/>
			<rc type="edit" ns="0" title="Cisnormativity" pageid="71996420" revid="1116192148" old_revid="1116189075" rcid="1559952702" timestamp="2022-10-15T08:59:05Z"/>
			<rc type="edit" ns="0" title="Steppe Arena" pageid="60625799" revid="1116192147" old_revid="1116191896" rcid="1559952701" timestamp="2022-10-15T08:59:05Z"/>
			<rc type="edit" ns="0" title="Marius Bülter" pageid="58074752" revid="1116192146" old_revid="1112795402" rcid="1559952700" timestamp="2022-10-15T08:59:05Z"/>
			<rc type="edit" ns="0" title="Edward Eager" pageid="1633389" revid="1116192145" old_revid="1114631450" rcid="1559952699" timestamp="2022-10-15T08:59:05Z"/>
			<rc type="edit" ns="0" title="Galp Energia" pageid="5969536" revid="1116192141" old_revid="1109334278" rcid="1559952696" timestamp="2022-10-15T08:59:03Z"/>
			<rc type="edit" ns="0" title="Kaltungo Emirate" pageid="72011272" revid="1116192142" old_revid="1116192102" rcid="1559952694" timestamp="2022-10-15T08:59:03Z"/>
			<rc type="edit" ns="0" title="List of Justice League episodes" pageid="2910641" revid="1116192139" old_revid="1116190255" rcid="1559952692" timestamp="2022-10-15T08:59:03Z"/>
			<rc type="edit" ns="0" title="Joy dol" pageid="71949265" revid="1116192143" old_revid="1116192014" rcid="1559952697" timestamp="2022-10-15T08:59:02Z"/>

Cells B3:B12 contain the formula:


Cells B3:B12 returns:

2022 NASCAR Cup Series
Steppe Arena
Marius Bülter
Edward Eager
Galp Energia
Kaltungo Emirate
List of Justice League episodes
Joy dol

Cells C3:C12 contain the formula:


Cells C3:C12 formated with a custom formats: yyyy-mm-ddThh:mm:ss.000Z, it returns:


You can also use FILTERXML in conjunction with the ENCODEURL and WEBSERVICE functions. For example, you can use the API service to return information about a company stock:


Where you have a stock ticker in cell C2. You can then use the FILTERXML function to extract specific data about that stock. For example, to get the Last Price of the stock ticker in cell C2, you can use:


Convert string to array

In cell A1 there is a string separated by ",":


Now, how can I convert A1 to an array using a formula?


IF you use MID function:

=TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",LEN(A1))),(ROW(INDIRECT("1:"&(LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1)))-1)*LEN(A1)+1,LEN(A1)))

But, if you use the FILTERXML function, it's very simple:


In this formula, you can replace "r" or "c" with your favorite string.

Leave a comment

Your email address will not be published. Required fields are marked *

Format your code: <pre><code class="language-vba">place your code here</code></pre>