MS Excel Text Into Columns
Have you ever found yourself wondering what in the world possessed the person who created your MS Excel file to put both first and last name in the same cell?
Now how are you supposed to sort that list alphabetically by last name when first name is listed first?
The answer is that you can’t—you have to separate the first and last names—somehow…
You could spend your time retyping the last name into the next column. It works but it’s not a great plan since it could take “forever” if your list is really long.
Or… maybe you have other text / data that really should be separated into different cells for easy sorting and editing.
Would you like a fast way to separate the data? (This works best with data that’s pretty consistent and not too complicated.)
Most of us would say “yes”. (Even if you don’t have an immediate use for this one, it’s one of those handy little pieces of information that may just get you out of a jam one day.
So let’s get to that faster way…
The first thing you’ll need to do is to highlight the data that needs to be separated.
Now go to the Data menu, Text into Columns choice.
A 3-step wizard will start to guide you through the process.
On step 1 check at the top that the data will be delimited.
Then click Next.
In step 2 you need to check the Space option in the Delimiters section. (Make sure all other choices are unchecked.) Basically what you’re doing here is telling Excel what character separates your pieces of data—so that when it find a space it knows to stick the next piece of text in a new column.
Did you notice the preview of your data in the bottom window? See how the names are now divided into different columns?
Step 3 will allow you to do some formatting and placement of your data—but for now, as beginners, just click the Finish button.
Just like magic your first and last names are in two separate columns, ready to sort – no retyping necessary.
(Ok—maybe not magic but, if your list was long, you’ll feel like it was when you realize how much time you just saved.)