Using Text to Columns to Separate Data
- 11 Comment
This post can be seen as a follow up to an earlier post, Separate Full Name into First & Last Name. Excel provides a very easy to use tool to separate delimited data into multiple columns. All that delimited data means is that your data is separated into different sections by a character(s), usually a tab, comma, percentage sign, etc. The same can be done with a macro or group of formulas, but it really is easy and convenient to use this built-in feature.
Revisiting the Full Name Separation Issue
As we talked about in the previous post, many times you will run across the need to separate a full name into first and last name. Let’s take a look at a few screen shots to walk us through this process:
Here’s our spreadsheet to begin with. You’ll see that we have full names in our first column. Our end goal is to have the first names in column A and the last names in Column B.
Next you’ll need to highlight Column A and go to Data–>Text To Columns and the window below will pop up.
Above is the Text To Columns wizard. In this example we want to choose “Delimited”, because our Full Name is delimited by a space between the first and last name. Hit Next…
By default, the Tab Delimiter is selected. Check the box marked “Space”. And click Next…
Finally, the wizard asked if you want to format the data at all. In our example, general formatting is just fine. So all we need to do is press “Finish”. And the result can be seen below.
Hopefully you can see the usefulness of this Excel feature. Many times you will receive data that is in a csv (comma separated values) file, where all of the data is in a text file that is separated by commas. In that type of file, you would select a comma delimiter instead of the space delimiter we used for the full names. If you can’t use the preset values that excel built, they also provide a place for different delimiter to split your data.