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: 


11 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
  11. Keith said:

    How would I separate this data into columns?

    16133 QT Achillea aegyptiaca v. taygetae 2.25 *NET pale yellow fls, grey lvs *B/B N
    16961 4FL Adiantum capillus-veneris 2.65 *NET Maidenhair Fern ** N
    17766 4FL Aloe ‘Fang’ 3.15 *NET green w/orange teeth * N
    17402 4FL Aloe ‘Latte’ 3.15 *NET reddish brown ** N
    17767 4FL Aloe ‘Moondance’ 3.15 *NET white leaves! *
    17795 4FL Aloe ‘Pink Blush’ 3.15 *NET pink bumps on leaves *
    17909 4FL Aloe ‘Thin Lad’ 3.15 *NET dark leaves, orange fls. *
    16638 4FL Aloe asstd. #1 3.15 *NET assorted varieties **
    17630 4FL Aloe nobilis 3.15 *NET compact, red fls **
    12049 QT Artemisia ‘Powis Castle’ 2.25 *NET silver-grey lvs, 2′ ** N
    17850 QT Artichoke Imperial Condor 2.65 *NET NEW’15 round & glossy! **
    13894 QT Aster lateriflorus ‘Prince’ 2.65 *NET dark purple leaf **
    16931 4FL Athyrium nipponicum ‘Pictum’ 2.65 *NET Japanese Painted Fern *
    16443 QT Blackberry ‘Marion’ 4.25 *NET long canes *
    16444 QT Blackberry ‘Natchez’ PP20891 4.25 *NET thornless, sweet *
    16445 QT Blackberry ‘Prime-Jan’ PP15788 4.25 *NET fruit the 1st year **
    17246 QT Calibrachoa ‘Calipetite Blue’ 2.25 *NET mid-blue *B/B
    17247 QT Calibrachoa ‘Calipetite Pink’ 2.25 *NET very compact *B/B
    16953 QT Calibrachoa ‘Calipetite Red’ 2.25 *NET bright red *B/B
    17248 QT Calibrachoa ‘Calipetite White’ 2.25 *NET compact *B/B
    17249 QT Calibrachoa ‘Calipetite Yellow’ 2.25 *NET cheerfullness *B/B
    13640 QT Calibrachoa ‘Minifamous Double Amethyst’ 2.25 *NET charming lavender fls *B/B
    17252 QT Calibrachoa ‘Minifamous Double Blue’ 2.25 *NET dark blue *B/B
    13645 QT Calibrachoa ‘Minifamous Double Magenta’ 2.25 *NET like little roses *B/B
    15705 QT Calibrachoa ‘Minifamous Double Pink Evolution’ 2.25 *NET double pink fls *B/B
    17250 QT Calibrachoa ‘Minifamous Double Red’ 2.25 *NET all season color *B/B
    17251 QT Calibrachoa ‘Minifamous Double White’ 2.25 *NET elegant white *B/B
    17964 QT Canna Cannova Mango 2.65 *NET *NEW* golden orange fls *
    17965 QT Canna Cannova Rose 2.65 *NET *NEW* blooms till frost * N
    17963 QT Canna Cannova Yellow 2.65 *NET *NEW* bright yellow fls *
    17962 QT Canna Tropical Bronze Scarlet 2.65 *NET *NEW* dark lvs, red fls **
    14212 QT Chrysanthemum parthenium ‘Aureum’ 2.25 *NET Golden Feverfew **
    12448 QT Coprosma ‘Evening Glow’ 2.65 *NET green, white, coral **
    18061 QT Cordyline fruticosa ‘Chocolate’ 4.25 *NET *NEW* dark black leaves * N
    12867 QT Cordyline fruticosa ‘Ruby’ 4.25 *NET broad red/maroon lf *
    16449 QT Dianthus allwoodii ‘Frosty Fire’ 2.25 *NET single red fls, grey lvs *B/B N
    14222 QT Dianthus barbatus ‘Heart Attack’ 2.25 *NET ‘black’ flowers **

    April 18th, 2015 at 12:43 pm


Subscribe Form

Subscribe to Blog

Sign Up for Excel Hints

Free updates with Excel Hints, Tips & Tricks