All language subtitles for 4. Splitting Data using Text to Columns

In the next couple of lessons, I'm going to show you some techniques for splitting up information,

and in this first lesson, the method that we're going to use is splitting up data, using text to columns.

Now what do I mean by splitting up data and why is this useful when you're cleaning your data?

Well, let's take a look at a very simple example.

First of all, and then I'll show you how you can use that on the sales spreadsheet that we've been

working on now on this first example.

If you take a look, I've got some information listed in Column A..

And this might be information that I've imported from something like a text file.

And you can see here it basically shows the employee name.

We then have a comma, which we call that delimiter.

We then have that person's job title, then a comma.

Then the department that they work in, then a comma and then their salary.

And this is a very realistic example of what data actually looks like if you imported from a text file

and don't break it up first.

So it's a very real possibility that you might import data and it does look something like this.

And what we basically want to do is we want to break up the data that we have in these cells so that

we have the employee name in one column, job title in another department and salary.

And there are a few different ways that we can do this type of thing.

Now we're going to use the oldest method in Excel, first of all, and that is by using text to columns.

So what I'm going to do is I'm going to select the data that I want to split up.

Let's jump up to the data ribbon.

And in the data tools group, we have a big old button here that says text to columns and the screen

tip says split a single column of text into multiple columns, and that is exactly what I want to do.

So let's click on this button.

Now this is going to basically take you through a convert text to columns with it.

And if you've ever used a wizard before, you'll know that kind of guides you through the process.

Now, the first thing we need to determine here is if our column is a limited or fixed width.

Now, the limited means that your different fields, so your different items, the employee, the department,

the job title, so on and so forth, how they are split up.

So the delimiter that I'm using here is a comma.

I have a comma separating each of my fields.

So I'm going to choose delimited and click on next on the next page.

This is where I can define the type of delimiter I'm using, because it might be that your fields are

separated with a tab, or maybe a comma, or maybe a semicolon or a space, or maybe some other item

entirely.

If you do have your fields separated with something that you can't see listed just here, you can choose

other and then specify in here how your fields are separated now, minus separated with a comma.

So let's choose that option.

And as soon as I do that, you can see in the preview window, it's now splitting my data wherever it

finds a comma.

So that is absolutely perfect.

Let's click on next.

Now here, if I wanted to, I could start applying some formatting to the columns.

Now I'm just going to leave my columns on the default, which is general.

The important thing here is the destination.

So this is where you specify where you want your data to be placed after it's been split up.

So I'm going to remove A4 from there because I want the first item to be in Cell C for let's click on

Finish.

And would you take a look at that very quickly?

It's broken everything up into nice, neat columns.

I can then just go in and do my final pieces of formatting.

So that is a very simple example of how you can use text to columns to break up data into multiple columns.

So now let's take a look at how we might use that on the sales data that we've been working on.

So on the next tab, I've actually made a small change to this worksheet just so I can show you this

example.

Now, if we take a look in Column A, what I've actually done here is I've combined the country with

the product and the product is listed after the country in brackets or parentheses.

So what I want to do here is I want to split up the country and the product into two separate columns.

So for this, I need to add two blank columns.

So control shift plus control shift plus.

And let's call this one country and this one product.

So now I can use text two columns to break up this data.

So the first thing I'm going to do here is control shift down to select all of the data.

Let's go up to the data tab and select.

Text to columns once again, this is going to be a limited.

But this is a bit different to how it was previously because what is separating my two fields, what

is separating the country and the product?

Well, I have a bracket in there, so I'm going to say that I want to split my data where it finds that

first bracket.

If you take a look at the data preview, that's fine.

I've got the country in one column.

I have the product in the next column, but I have a trailing bracket.

Now we're going to deal with that afterwards.

Let's just get this split up, first of all.

So let's click on next and my destination.

Let's remove that.

It's going to be Selby, too.

Now it might be that you get this error that says there's already data here now.

I don't have any data there.

Let's click on OK, and it's still going to put that in.

So now what I can do is I can basically delete out this first column.

Now, the final thing we need to deal with here is this trailing bracket that we have in this column.

We don't need that to be there, so I can get rid of this doing a simple find and replace.

And what that will do is it will allow you to specify what you want to find in this case, a closing

bracket and you can say what you want to replace it with.

And I'm going to replace it with a blank, which will effectively remove it.

So I'm going to select Column B and press control F. Now I'm going to jump across to the Replace tab

and I'm going to specify what it needs to find.

So I'm looking for a closing bracket and I want to replace it with nothing.

So I'm going to leave this blank and replace or it's made 702 replacements and it's got rid of that

closing bracket.

So that is how I could use text to columns.

In a more practical example, in the next lesson, I'm going to show you how you can do the same thing,

how you can split up data, but this time using Excel functions.