Splitting cell contents

Hey there, my name is Dan. In this video we're going to split our names here, they're kind of bunched together in one cell. And presto, turn them into our two column thing. Super easy, let's go and do that now.

There's two ways of doing it, there's the regular way, it's called 'Text to Columns', pretty easy. And there's another one called 'Flash Fill'. I'm going to show you 'Flash Fill' afterwards because I'll introduce this 'Flash Fill' to you. I like to say 'Flash Fill' loads. And in fact, 'Flash Fill' has so many other kind of cool uses. So let's do the first one.

What I want to do is, I want to split the first name and last name. I'm going to select this column here. Actually, first of all, I want to separate them into two columns. I haven't got a second column here, so I'm going to right click 'B'. And go to 'Insert', so I've got a nice blank one here. I'm going to select 'A', and I'm going to make sure I'm on my 'Data' tab, on top here. And there's one that says 'Text to Columns'. 'Delimited', click 'Next'.

What spaces, what delineates these two words? In my case it's a space between them. You might have maybe a comma between them. Mine's a space, you can kind of tell down here, that little preview. Pick whatever separates them out. Mine's space, I'm going to click 'Next'. Then I'm going to click 'Finish', and magic. You can see, it's separated my 'First Name' and 'Last Name'.

That could be cool if you've got, maybe cities, states, and post codes as you can separate them all out.They've got spaces between them. So that's one thing, it works good. What I want to do though is I'm going to 'undo' that. I'm going to insert another 'column' because I want to show you something called 'Flash Fill'. I love it, I love saying it, it does awesome things.

Think of it as, you teach it once what to do, and then it kind of goes "Oh, you probably mean to do this over and over again, and I have learnt." It's pretty cool.

So what you do is-- I've got 'Cynthia Wolfe' here. Let's say I want to separate them out. I can do a couple of things at the same time, though I can say, actually, I want it to be 'Cynthia', I'm typing this in. You can see, I'm using title case so I'm putting 'Cynthia' in there, but I'm using uppers and lowers. And then her name 'Wolfe' here. Nothing really happens, except when I click here, still nothing happens, make sure you're on 'Data'. And click on 'Flash Fill'. And it just kind of guesses what you were doing.

You can see, it's pulled the first name, and it's changed the case at the same time. Same here with 'Wolfe'.And you say 'Flash Fill'. And it goes through and reaches into these ones here. I love 'Flash Fill'.

So there's lots of uses for this when you're re-ordering data. Let's say you want it to be-- still you want it to change the case, but you want it to be like this where they're still together, like they are, but they're re-ordered so their last name is first because it's easier to sort like that. I've switched them around, put a comma in there as well. And underneath it, I go 'Flash Fill'. Cool, huh?

So we reached in, grabbed them, sorted them where I want. And you can do that with any data. Just give it a shot, it works most of the time. Make a column next to it, re-order it the way you want. Next row underneath, and just click 'Flash Fill', and see how it goes. That's it for 'Flash Fill'.

I'm just going to tidy up my columns down, you can skip along to the end. What I want to do is, I need them separated. So I'm going to 'undo', get rid of this one. And I'm going to say I want the first name first, I wanted 'Cynthia'. 'Tab' across, 'Wolfe'. You can't do two of these at once. So you might just select both of these and go to 'Flash Fill'. But you can't click 'Flash Fill' when there's two of them. So you got to do this one, that one.  And what I'll do is, this would be 'First Name', 'Last Name'. And this column here, I'm going to 'Delete'. 'Bold' them up to match the rest of them.

And that will get us on to the next video. Let's hit 'Save'. I will see you in the next video, I promise not to sing the word 'Flash Fill' in any more videos. I've got it out of my system. All right, see you in the next one.