Today, we are going to discuss about splitting a sentence into multiple cells in excel.
There are lot to learn, so let's dive into the topic without any delay.
Please see below pic., to understand our requirement and result.
In our last post, we used powerful text functions to accomplish the same, but here we are going to discuss about very unspoken but yet very effective FILTERXML function. (Which is available from Excel 2013 version).
The arguments of FilterXML function are,
=FILTERXML(xml,xpath)
The function we used in D4 cell is,
=IFERROR(FILTERXML("<All><Text>"&SUBSTITUTE($B4," ","</Text><Text>")&"</Text></All>","/All/Text["&COLUMNS($D$4:D4)&"]"),"")
Before getting into the function, let me give some introduction about xml argument.
If we look into the B4 cell, it contains 4 words with space as the delimiter, in some text files we can differentiate the fields by the tab or comma as the delimiter.
In xml language, there is a container for each word and each sentence which acts as a delimiter. Every word starts with the container<a> and ends with the container</a> and every sentence starts with<b> and ends with</b>. We can use any text instead of 'a', but the same has to be called at the end with '/'.
Same sentence will look like below in xml language.
Here I used the container'<All>' for the sentence, and it ends with'</All>'.
For each words it starts with'<Text>' and ends with '</Text>'.
To convert our existing sentence into xml format, we have to add"<All><Text>" at the beginning, then we have to substitute the space with "</Text><Text>" and at the end we have to add "</Text></All>". Then we can use 'Text' container to extract elements in 'All' based on the position. If we see the formula version of conversion, it will be easy for you to understand. So, let's dive in.
In xml argument, start with "<All><Text>" and add joining operator '&' (Ampersand). To replace the space with our container, we are going to use the substitute function.
Arguments of the SUBSTITUTE functions are,
=SUBSTITUTE(text,old_text,new_text,[instance_num])
For the First argument text, we fed B4 cell,
In the old_text argument, we fed space with in double quotes(" "),
In the New_text argument, we fed "</Text><Text>",
Instance_num argument is optional and in our case all the spaces need to be replaced and so we can ignore that argument.
After closing the brackets for substitute function we have to concatenate with "</Text></All>" using joining operator '&' (ampersand).
The xpath argument which makes the Filterxml function more powerful. Here we can use more than 20 methods to play with parent and children elements.
Based on our requirement, we are going to use 2 methods to call the children elements.
Method 1:
"//Text[1]"
In this method, the first element will be extracted. To make it dynamic, we used columns function with expandable range $D$4:D4. Here the first reference is locked in both row and column, it is absolute reference. So the reference will not move as we drag the formula across columns. The 2nd reference is not locked in both row and column, it is relative reference. It will move as we copy the formula across columns. In the D4 cell, the range is $D$4:D4 and so the columns function will deliver 1, In the E4 cell the range is $D$4:E4 and so the columns function will deliver 2 and so on.
Method 2:
"//child::Text"
In this method, we can extract each element of text, to populate across column we wrapped it in transpose function,
See below pic,
There are lot to do with FILTERXML function using xpath argument, that we will discuss in our later posts.
Comments