Afrikaans
Akan
Albanian
Amharic
Arabic
Armenian
Azerbaijani
Basque
Belarusian
Bemba
Bengali
Bihari
Bosnian
Breton
Bulgarian
Cambodian
Catalan
Cebuano
Cherokee
Chichewa
Chinese (Simplified)
Chinese (Traditional)
Corsican
Croatian
Czech
Danish
Dutch
English
Esperanto
Estonian
Ewe
Faroese
Filipino
Finnish
Frisian
Ga
Galician
Georgian
German
Greek
Guarani
Gujarati
Haitian Creole
Hausa
Hawaiian
Hebrew
Hindi
Hmong
Hungarian
Icelandic
Igbo
Indonesian
Interlingua
Irish
Italian
Japanese
Javanese
Kannada
Kazakh
Kinyarwanda
Kirundi
Kongo
Korean
Krio (Sierra Leone)
Kurdish
Kurdish (Soranรฎ)
Kyrgyz
Laothian
Latin
Latvian
Lingala
Lithuanian
Lozi
Luganda
Luo
Luxembourgish
Macedonian
Malagasy
Malay
Malayalam
Maltese
Maori
Marathi
Mauritian Creole
Moldavian
Mongolian
Myanmar (Burmese)
Montenegrin
Nepali
Nigerian Pidgin
Northern Sotho
Norwegian
Norwegian (Nynorsk)
Occitan
Oriya
Oromo
Pashto
Persian
Polish
Portuguese (Brazil)
Portuguese (Portugal)
Punjabi
Quechua
Romanian
Romansh
Runyakitara
Russian
Samoan
Scots Gaelic
Serbian
Serbo-Croatian
Sesotho
Setswana
Seychellois Creole
Shona
Sindhi
Sinhalese
Slovak
Slovenian
Somali
Spanish
Spanish (Latin American)
Sundanese
Swahili
Swedish
Tajik
Tamil
Tatar
Telugu
Thai
Tigrinya
Tonga
Tshiluba
Tumbuka
Turkish
Turkmen
Twi
Uighur
Ukrainian
Urdu
Uzbek
Vietnamese
Welsh
Wolof
Xhosa
Yiddish
Yoruba
Zulu
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.
Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.