Up being that's all over now because tech split takes care of it all you can even combine it with other functions to make excel do stuff that we could never do before i guess we just want to see it.
Right so let's get to it let's start with the basics.
So here i'm collecting different skills from different people and everything is in a single cell i want it split into multiple cells no problem for text split i'm just going.
To start typing text split it's right here i need my text which is this one and then i have to define my delimiter based on what do i want to split these well in this case it's a comma so i'm.
Space that comes after these now you have different options to get rid of this space you could put this function inside the trim function and that's going to trim out that space.
Or just going to press ctrl z to go back you could adjust your delimiter to be a comma space you end up with the same result what's the problem when i copy this down.
Is that going to work everywhere no because some people used a semicolon as a delimiter and that ends up messing my results well no problem for tech split because.
You can add multiple delimiters in here you just have to put it inside curly brackets i'm going to find my curly brackets on the keyboard we need to close this as well.
And inside we are going to define our multiple delimiters i'm just going to add my separator and inside the quotation marks i'm going to add my second delimiter.
Posts Related:
Which in this case is a semicolon space
And close the quotation mark you can add as many delimiters as you want to be on the safe side why not add a colon as well.And press enter now when i pull this down everything is split properly if i happen to add a colon to excel here it works properly.
Next up let's look at a case where we have to split the name into first and last but we don't want to get this mr and mrs in there we're going to use text split.
The text is this and we have a space as a delimiter now if i just leave this as is and press enter i do end up with that mr and miss in there but remember what we saw before.
I can add multiple delimiters so i could treat these mr and misses as their own delimiters so in quotation marks i'm going to put mr and let's add another one.
For miss and let's close that curly bracket now when i press enter i get everything split but i end up with this empty cell here i want to ignore.
Empty cells well that's not a problem because text split has multiple arguments that are optional we have a row delimiter we don't need that right now but we need this one ignore empty.
Cells we are going to put true for that and now when we press enter we have first and last names in this case we have our data written like this in a single cell and we want.
To bring it into multiple cells that looks like this no problem for text split let's start with text split our text is this now we can make use of the column.
Delimiter and the row delimiter
So what do we want the separator of the columns to be it should be the equal sign so let's put that for our column.Delimiter next is the row delimiter that should be the comma i'm going to go with comma space and close the bracket press enter and i get everything spilled properly.
Next up let's do something really cool we want to take our skills here and we want to get them back in a single cell but we want them internally sorted so this one should have excel first and.
Then google sheets then power bi powerpoint and word last how do we do that well we're going to use a few functions together to get what we want.
First i'm going to start from the inside we're going to use text split to split these up that's our text my delimiter here is a comma but here i.
Noticed i don't have a space whereas i have a comma space there so to be on the safe side i'm just gonna go with a comma as the delimiter but i'm gonna put this inside the trim function so that i don't.
Have any space at the beginning of the words now that i have everything split separately i can use the sort function and get these sorted.
My array is this my sort index is just the one row that i have so i'm just going to skip that argument the sort order default is ascending i'm going to skip that argument because i.
Want the default but this is something i don't want to skip and i can't skip because i am sorting by columns in this case so i need a true for that last argument i close the bracket press enter.
I have everything sorted but i don't want them on separate cells i want to bring everything together again into a single cell so i'm going to use the text join function and stitch these back.
Together my delimiter is a comma space in quotation marks i can ignore empty cells in case i have nothing or just the space between the delimiters and last is my.
Text which is the formula i wrote before close the bracket press enter and we have everything internally sorted we get excel first then oracle power bi.
And last is sap amazing functions that help us do amazing things so as you can see this function is like the formula version of text to columns.
Feature that we have in excel except it's smarter and it's a function so it's dynamic what do you think about this are you excited about this development let me know in the comments as usual thank.
You for watching thank you for being here subscribe if you aren't subscribed and i'm gonna see you in the next video .
