SPLIT Values into Multiple Columns using FILTERXML

Welcome to Excel Jet Consult blog post. In this tutorial, we will learn how to split values from single column to multiple columns using FILTERXML Function

Kindly watch the video below for more explanation


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


Syntax:  =FILTERXML(xml, xpath)

xml – eXtensible Markup Language represent a string in valid XML format

xpath – represent a string in standard xpath format


Sample Data

We have the sample data in the caption below. We can see that there is Month-Year-Customer Type-Account Manager-Product Category-Unit Sold-Price-Total and each of the data follow the same pattern. Based on the delimiter which is hyphen, we will do the split

Split Values

  • In cell B2, execute the formula: =TRANSPOSE(FILTERXML(“<y><z>”&SUBSTITUTE(A1,”-“,”</z><z>”)&”</z></y>”,”//z”))

Based on the formula, I deployed angle brackets with y and z inside double qoute. In addition, I deployed SUBSTITUTE function inside the function and finally wrapped the formula inside TRANSPOSE function. Conversely, instead of using y and z, we can use cell reference (see the video)

  • Click Enter and copy down the formula

In the caption below, we can that we have successfully split the values into several columns.


Although, there is no split function in Excel currently, we have see that it is possible to achieve split using different method. If you enjoy this tutorial, kindly subscribe to my YouTube Channel: Abiola David Excel Jet Consult

Posted in Blog and tagged , , .

Leave a Reply

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