Text to multiple cells so no matter how many spaces you have in that text you can forget about left right mid functions and that's not all you get access to many other useful functions.
But before we jump in remember that it's going to take some time for these functions to get rolled out to office 365 users check the description of this video for an update on availability now.
I can't wait to show these to you so let's just jump in let's start with the amazing two call and two row functions so here i have names in multiple columns.
And i want to combine everything into a single column i can easily do that with a new to call function i'm just going to select my array that's it close the bracket press enter and i have.
The two row function is very similar to this except that you get your values spilled into the row so if i go with two row here select my range close bracket press enter i get everything spilled.
Horizontally now again you can put this in the unique function as well but this time we just need to specify that our values are in column so we have to put true here close bracket press enter and.
We get a horizontal unique list next up we have the text split function so here i have a list of full names and i want to split them into separate columns i'm just going to start typing.
Text split and we have this amazing function which you can do a lot with and i'm going to create a separate video on this but here's the simplest use you select your text and then you define.
Your delimiter basically based on what do you want the text split in this case based on a space i'm going to put it in quotation marks and that's it check this out i press enter i get walter tobias.
Posts Related:
Miller split into three separate columns
Let's drag this down and i have my names split properly now we also get two other similar functions that help us work easier with.Text we have text after and text before so let's go with text before if this is my text and i say the delimiter is a space what do i get.
I get the first name so by default it gives you the first instance but if you want everything before the second space you can go with a 2 here i'll just put this back to a 1.
And drag this down so this is similar to the left function except that it's a bit more flexible because it allows you to define your delimiter we also get text after.
Here this is our text and after a space and let's say after the first instance we get everything after the first name if i change this to 2 it's everything.
After the second instance now of course you can use a function to get this to be dynamic or use the if error function to avoid errors like this next up we have vstack and hstack these.
Allow you to append your data together here i have two separate ranges for name and salary now these could be in separate sheets and i want to append them on top of one another i'm going to.
Use v-stack for vertical stacking array 1 is this and array 2 is this you can of course add multiple arrays i'm going to press enter and i get all of them together.
So imagine the possibilities if this was a table i'm just going to press ctrl t convert it to a table and this was a table as well and i end up with a new name here.
Check what happens to my results they
Update automatically what does each stack do well it does a similar thing except it spills the data horizontally so i'm just going to press.Ctrl z and remove these names and let's do it right here start off with h stack array one let's use our tables now is this one array two is this one close bracket press enter and we get them.
Appended beside one another next up we have wrap rows and wrap columns so i have my data here in a single row and i want to put these below one another so this should come below.
The names and this should come below that i can easily do that with the new wrap rose function you define your vector in this case it's this range and then the wrap count so from where do you.
Want it to start wrapping well after the third value here so put a three close bracket press enter and i get my data wrapped properly how does it look with wrap columns well it does a similar.
Thing we're going to select our range and then decide the wrap count for our column so if i select three here my names are gonna end up in the same column like this.
Next up we have the take and drop functions with these functions you get to keep or drop the parts of your data set that you don't want so here for example let's say i just want to keep.
The top three rows i'm going to start off with the take function define my array and for rows just put in a 3 close bracket press enter and i get the first three rows if i want the last three rows.
I'm gonna put a minus three what if i just wanna keep two columns i'm gonna add a two for the column press enter and i get to keep the first two columns this data isn't sorted so let's say i.
Want to get the values based on the top three salaries well that's not a problem because i can put my range inside the sort function that's what i want to sort i want to sort this based on the salary.
Column which is in the third column and i want it in descending order i'm going to close the bracket press enter and i get the records with the top three salaries if i only want to keep the two.
Columns put a two here and we're done what about drop well drop works in a similar way except that you get to drop whatever you define if i select my range here and say i want to drop three it's.
Gonna drop the first three rows if i say minus three it's going to drop the last three rows and of course you can combine this with other functions like we did with take.
Next we have choose rows and choose calls so we choose calls which is probably going to be the more commonly used one you get to define your array and then.
The columns that you want to keep so you can define them based on an index number let's say i want to keep column one and column three only because i just want the names and salaries back i'll put a.
One and a three press enter and i get names and salaries back now you can probably picture the potential of this what if i didn't want everything returned but only the.
Salaries that are above a hundred thousand well i could combine this with a filter function so for my range here i'm going to get it filtered before i choose what.
To return this is my array what i want included are salaries that are greater than a hundred thousand now close the bracket for the filter function and press enter.
And i get to choose what i want returned we choose calls now how does choose rows work well it works in a similar way you get to define your array and then you get to.
Define the rows that you want to keep so let's say i want to keep the first row the fifth row and the sixth row close bracket press enter and that's what i get.
Last we have the expand function with expand you get to expand your range and add additional columns or rows so for example here i have department and name i want to add an extra column for.
Salary and i want that populated with the word missing so i'm going to start off with the expand function select my range here for rows i want it to be as many rows as.
I have so i'm just going to skip that argument for columns i want to add an extra column i already have two columns i'm going to put a three then if i don't put anything for the pad width close the.
Bracket press enter i get an extra column appended but with errors i don't want errors instead i want the word missing so i'm gonna add missing in quotation marks for the last argument.
When i press enter i get missing there if you wanted to add extra rows to this so i have three currently so let's say i go with four and i press enter i get missing on the bottom here as well in.
This case i don't want i'm just gonna remove that and enter now i'm sure this is gonna come in handy when you want your ranges to be of the same size inside other functions that work with.
Arrays so just to demonstrate a simple example based on what we have if i want to stack this on top of another one and i still want missing there i'm just going to wrap this in the v stack.
Function that's my array 1 and my array 2 let's just jump quickly to another sheet this is the take sheet i'm going to select this range close bracket press.
Enter and i get my results organized in a nice way that was a quick introduction to the new functions and i really want to know what your favorite one is so please share in the comments below i.
