UserVoice viewers have been asking Microsoft for a TEXTSPLIT function. Thanks to a comment from Daniel Dion, it turns out that we've had a function to do TEXTSPLIT since Excel 2013! In this episode, we break down the FILTERXML function in Excel.
Transcript of the video:
Learn Excel from MrExcel podcast episode 2355.
We don't need TEXTSPLIT! We've had it the whole time with FILTERXML.
Hey, welcome back to MrExcel netcast. I am Bill Jelen.
Today's question is from me. "Hey, Microsoft.
When are we finally going to get a TEXTSPLIT function like TEXTJOIN? Not just me. Look at this.
221 people have voted for this out on Excel dot UserVoice dot com.
It turns out we don't need TEXTSPLIT.
The last dueling Excel podcast, I was trying to break this data apart. Check out this great comment from Daniel Dion.
He says, "Just use this formula".
I am like "What?" And then Gorflunk says "That's money!" Hell, yeah. That's money.
This is an amazing function.
Let's break this down. How FITLERXML is doing what TEXTSPLIT should do.
So right here, this is taking those numbers and it is forming it into XML code. so there's an X-element. Then a Y-element.
That is 1444. End-Y. Then Y-element. That is 1445.
End-Y. [ Da-da-da-ta-da ] And then finally an End-X element. How do we do this?
It just starts out with x and y in brackets. And then the SUBSTITUTE of this number.
Changing every comma to an End-Y, Start-Y.
And then at the end an End-Y and an End-X. Now we join all of that together. So the original text here.
The SUBSTITUTE and the closing text.
I can imagine getting to the point where I can remember this.
I mean, it will take a little bit of thinking the first 10 or 20 times I do it.
But, eventually this would become second nature. Then the awesome thing.
Once we have XML, then =FILTERXML. That's the XML.
And the xpath we want is slash slash y. Close quote. Close parenthesis.
And we get just the numbers split out.
Now what I really would want here is i would want them to go sideways.
So I am probably always going to wrap this in TRANSPOSE. Like that. To have it go across.
But check that out. There is my TRANSPOSE, FILTERXML.
I want to be funny here and say, "Hey Joe McDaid, we don't need TEXTSPLIT!" Although, look how much easier TEXTSPLIT would be. My vote at UserVoice still stands.
Microsoft: we need TEXTSPLIT some day. Some year.
Hopefully, they'll get that for us.
Hey, while we're talking about subscriber. Here is another thing even for those of you who subscribed.
Check out Daniel and Gorflunk.
That little symbol there that tells me that they are subscribers.
But a lot of you are subscribers and based on the default settings in YouTube, I don't get to see it.
I don't EVER want to start a membership channel where you have to pay for special features.
But I appreciate the people who Subscribe.
Although, right now, I know a lot of you are subscribers and I don't see that icon in YouTube.
If you come up here to your avatar icon in the top right hand corner of YouTube. Then go down to settings.
Inside of Settings, go to Privacy.
Then Keep All My Subscriptions Private. If you turn that off.
Then all the people you subscribe to will know that you're a subscriber.
And they will feel special about you.
They will feel cool that you Subscribe.
And that you are helping us to get better YouTube benefits from that 50K, 100K, or 500K subscriber level.
It's up to you whether you want everyone to know who you subscribe to, but the Creators that you've subscribed to sure will appreciate it.
Well, hey I want to thank Daniel Dion for that awesome formula.
I want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.com.
We don't need TEXTSPLIT! We've had it the whole time with FILTERXML.
Hey, welcome back to MrExcel netcast. I am Bill Jelen.
Today's question is from me. "Hey, Microsoft.
When are we finally going to get a TEXTSPLIT function like TEXTJOIN? Not just me. Look at this.
221 people have voted for this out on Excel dot UserVoice dot com.
It turns out we don't need TEXTSPLIT.
The last dueling Excel podcast, I was trying to break this data apart. Check out this great comment from Daniel Dion.
He says, "Just use this formula".
I am like "What?" And then Gorflunk says "That's money!" Hell, yeah. That's money.
This is an amazing function.
Let's break this down. How FITLERXML is doing what TEXTSPLIT should do.
So right here, this is taking those numbers and it is forming it into XML code. so there's an X-element. Then a Y-element.
That is 1444. End-Y. Then Y-element. That is 1445.
End-Y. [ Da-da-da-ta-da ] And then finally an End-X element. How do we do this?
It just starts out with x and y in brackets. And then the SUBSTITUTE of this number.
Changing every comma to an End-Y, Start-Y.
And then at the end an End-Y and an End-X. Now we join all of that together. So the original text here.
The SUBSTITUTE and the closing text.
I can imagine getting to the point where I can remember this.
I mean, it will take a little bit of thinking the first 10 or 20 times I do it.
But, eventually this would become second nature. Then the awesome thing.
Once we have XML, then =FILTERXML. That's the XML.
And the xpath we want is slash slash y. Close quote. Close parenthesis.
And we get just the numbers split out.
Now what I really would want here is i would want them to go sideways.
So I am probably always going to wrap this in TRANSPOSE. Like that. To have it go across.
But check that out. There is my TRANSPOSE, FILTERXML.
I want to be funny here and say, "Hey Joe McDaid, we don't need TEXTSPLIT!" Although, look how much easier TEXTSPLIT would be. My vote at UserVoice still stands.
Microsoft: we need TEXTSPLIT some day. Some year.
Hopefully, they'll get that for us.
Hey, while we're talking about subscriber. Here is another thing even for those of you who subscribed.
Check out Daniel and Gorflunk.
That little symbol there that tells me that they are subscribers.
But a lot of you are subscribers and based on the default settings in YouTube, I don't get to see it.
I don't EVER want to start a membership channel where you have to pay for special features.
But I appreciate the people who Subscribe.
Although, right now, I know a lot of you are subscribers and I don't see that icon in YouTube.
If you come up here to your avatar icon in the top right hand corner of YouTube. Then go down to settings.
Inside of Settings, go to Privacy.
Then Keep All My Subscriptions Private. If you turn that off.
Then all the people you subscribe to will know that you're a subscriber.
And they will feel special about you.
They will feel cool that you Subscribe.
And that you are helping us to get better YouTube benefits from that 50K, 100K, or 500K subscriber level.
It's up to you whether you want everyone to know who you subscribe to, but the Creators that you've subscribed to sure will appreciate it.
Well, hey I want to thank Daniel Dion for that awesome formula.
I want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.com.
