Sometimes you have a single column with several fields joined together. For example, you might have city, state, zip code in a single column. Episode 417 shows how to break this data into three columns using the Text to Columns command.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Welcome back to the MrExcel netcast, I'm Bill Jelen.
Right at the end of last year, at the end of 2006, we talked about how to take data that was in two columns, and put it together in a single column.
Now what if we need to do the opposite effect?
We need to break that single column into two different columns.
Well, that's called Parsing Data, and Excel gives us a couple of great tools for doing that.
Today, I have a data set here with name in column A, and more data in the additional columns, and I want to break the name to the first name and last name.
This is always tough to do, because sometimes we have people that have multiple, either first names or last names.
So what I'm going to do is I'm going to insert a few extra columns, so I'm going to add three columns just be safe.
Select my data set, and then on the Data menu I'm going to choose the Text to Columns command.
And, I can either say that my data is Delimited, which means that there's something breaking each word apart, in this case a space, or that my data is Fixed-width.
If you're getting data from an old mainframe COBOL system, and lots of times you'll see the fixed-width data.
But today the data is Delimited, click Next.
On Step 2, we have to say what the delimiter is, and in this case we're going to use a space, that will basically break every word into a new cell, I'll uncheck the Tab key.
On the third step of the wizard we can specify our data is General or Text or Date.
Really try and stay away from choosing Text in the Text to Columns wizard, because that will convert the entire column to text, and you'll never be able to insert a formula in that column, let's just leave it as General.
The only reason why you would leave text is, if you need to keep some leading spaces there, that's the one time you'll choose Text.
I'm going to have my data go right over top of the original data in A2, will click Finish, and Excel breaks everything apart.
Now I need to head over to the column where I don't expect there to be any data, in this case column C. I expect first name in column A, last name in column B, I don't expect anything in column C, so I want to head to column C, hit the End key and the down arrow key, and see if I find any data.
So here sure enough, there was a guy Conrad and nickname Cal after the space.
So I need to change that, maybe copy his last name from column C over to B, and then also down here, there's another one where they had the last name and Jr, so I want to manually fix those.
I'm basically just cruise through that extra column and see if there's any data that showed up over there.
Now this was relatively simple because we had everything separated by a space.
Let's say we have addresses, where we have city, a comma, 2-letter state abbreviation, a space, and a zip code.
If we need to split that apart, it might be best to do it in two steps.
The first step would say that our delimiter is the comma, that way our city gets separated from the state and zip code.
So Data, Text to Columns, I'll choose Delimited, on the second step I change it to say it's a comma, take the space out, click Finish.
That way the cities that have two words, Mill Valley, St Joseph, end up in the first column.
Now I'll reselect the data in column B, use Data, Text to Columns, this time saying the delimiter is a space, and that should hopefully get the state and the zip code separated.
Now you'll notice that all my data here starts with a space, so in step 3 I want to choose that first column, and change it to “Do not import”.
That way I'll have states in column B and zip codes in column C, click Finish, there we go.
After a couple of Text to Columns, I was able to split the city, state, and zip, into three columns, for use in a mailing program or whatever I need to do.
Hey, thanks for stopping by, we’ll see you next time for another podcast from MrExcel!
Right at the end of last year, at the end of 2006, we talked about how to take data that was in two columns, and put it together in a single column.
Now what if we need to do the opposite effect?
We need to break that single column into two different columns.
Well, that's called Parsing Data, and Excel gives us a couple of great tools for doing that.
Today, I have a data set here with name in column A, and more data in the additional columns, and I want to break the name to the first name and last name.
This is always tough to do, because sometimes we have people that have multiple, either first names or last names.
So what I'm going to do is I'm going to insert a few extra columns, so I'm going to add three columns just be safe.
Select my data set, and then on the Data menu I'm going to choose the Text to Columns command.
And, I can either say that my data is Delimited, which means that there's something breaking each word apart, in this case a space, or that my data is Fixed-width.
If you're getting data from an old mainframe COBOL system, and lots of times you'll see the fixed-width data.
But today the data is Delimited, click Next.
On Step 2, we have to say what the delimiter is, and in this case we're going to use a space, that will basically break every word into a new cell, I'll uncheck the Tab key.
On the third step of the wizard we can specify our data is General or Text or Date.
Really try and stay away from choosing Text in the Text to Columns wizard, because that will convert the entire column to text, and you'll never be able to insert a formula in that column, let's just leave it as General.
The only reason why you would leave text is, if you need to keep some leading spaces there, that's the one time you'll choose Text.
I'm going to have my data go right over top of the original data in A2, will click Finish, and Excel breaks everything apart.
Now I need to head over to the column where I don't expect there to be any data, in this case column C. I expect first name in column A, last name in column B, I don't expect anything in column C, so I want to head to column C, hit the End key and the down arrow key, and see if I find any data.
So here sure enough, there was a guy Conrad and nickname Cal after the space.
So I need to change that, maybe copy his last name from column C over to B, and then also down here, there's another one where they had the last name and Jr, so I want to manually fix those.
I'm basically just cruise through that extra column and see if there's any data that showed up over there.
Now this was relatively simple because we had everything separated by a space.
Let's say we have addresses, where we have city, a comma, 2-letter state abbreviation, a space, and a zip code.
If we need to split that apart, it might be best to do it in two steps.
The first step would say that our delimiter is the comma, that way our city gets separated from the state and zip code.
So Data, Text to Columns, I'll choose Delimited, on the second step I change it to say it's a comma, take the space out, click Finish.
That way the cities that have two words, Mill Valley, St Joseph, end up in the first column.
Now I'll reselect the data in column B, use Data, Text to Columns, this time saying the delimiter is a space, and that should hopefully get the state and the zip code separated.
Now you'll notice that all my data here starts with a space, so in step 3 I want to choose that first column, and change it to “Do not import”.
That way I'll have states in column B and zip codes in column C, click Finish, there we go.
After a couple of Text to Columns, I was able to split the city, state, and zip, into three columns, for use in a mailing program or whatever I need to do.
Hey, thanks for stopping by, we’ll see you next time for another podcast from MrExcel!
