Jan 29 2010

Using Text to Columns to Separate Data

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:

Full Names

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.

Random Posts

Did you find this post helpful? Try signing up for our free daily tips.

Enter your email address: 


10 Comments on this post


  1. Automobile India said:

    Always save a copy of your original data in a new tab or worksheet. That way, if something goes wrong with your data manipulation, you still have your original data.

    March 3rd, 2010 at 1:30 am
  2. Nyoke said:

    Thank’s a lot. That’s so much helpful.

    March 24th, 2010 at 12:42 am
  3. Automobile India said:

    Wow very impressive sharing i also work this sheet or .xls sheet i love very much or very interstice for doing work.

    April 1st, 2010 at 11:09 am
  4. Automobile India said:

    I need to find and delete the rows where those addresses are the same. Is there an easy way to do this??

    July 1st, 2010 at 4:52 am
  5. Experts Comment said:

    This link provides good explanation to figure out how Text to column works


    September 30th, 2011 at 11:47 am
  6. Rony said:

    Hi! I have posted something similar, but in Excel 2007:

    January 2nd, 2012 at 11:21 pm
  7. Kristal said:

    I dont have the Data tab. How can I get that to show up?

    February 17th, 2012 at 9:18 pm
  8. Jennifer said:

    Where do I find date->text to columns

    August 28th, 2012 at 5:14 pm
  9. Jose said:

    Thanks, It was very helpful

    January 22nd, 2014 at 7:19 pm
  10. James HS said:

    I have a worksheet which contains data obtained from the web.
    I need to copy and paste information, including the source URL.

    The rest of the data pastes with its layout perfectly (using Paste>Unicode).

    However, I found that the URL pasted incorrectly. In every case, even when pasting a URL separately from the above process, the URL split at the colon “:” after the http, and after each period “.” regardless of the paste type that I used.

    After some thought, I discovered that the Text-To-Columns by Delimiters option that I had used an hour or so before was automatically operating on the URL as I pasted it.

    I resolved the problem by repeating a Text-To-Columns by Delimiters on a cell, and de-selecting all the delimiters. Of course, the Text-To-Columns did not change the cell.

    But, from that point on, I was again able to paste the URL as a single string of characters in one cell.

    January 23rd, 2014 at 3:45 pm


Subscribe Form

Subscribe to Blog

Sign Up for Excel Hints

Free updates with Excel Hints, Tips & Tricks