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
FILTERXML Function
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.
Conclusion
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