Thursday, February 3, 2011

Text to Columns - Excel 2007

A worksheet containing data in one column; you may find it necessary to separate the cell contents into two columns. In the image below, you maybe required to separate the first and last names into two columns, when using the data for a mail merge.


1.  Select your data, in this example A2:A10

2.  From the Data tab, within the Data Tools group, click on Text to Columns.

Text to Columns button






3.   From the Convert Text to Columns Wizard dialog box, select Delimited and click on Next.

4.  From the Convert Text to Columns Wizard dialog box, select Space, clear tab check mark and click on Next.










5.  From the Convert Text to Columns Wizard dialog box, select Text, click on the second column of data, select Text and click on Finish.


In the image below, finished results show data moved last names into it's own column.












Excel is awesome- it helps you work smarter, not harder.  Can this tip help you?

3 comments:

  1. Thanks, this method works great for data that is already in excel, what about formatting the cell knowing what kind of information your are culling, so that when it gets there, the data goes into the designated cell.
    Example, I am scanning the driver's licenses of my neighboorhood with a card reader. I know I can manage the data after I get it, but I wish for it to separate as it goes in, using a standard format.
    Can that be done with excel?

    ReplyDelete
  2. yes these steps works good . also check my page for video version

    How to Split names & values by using the Convert Text to Columns wizard in excel 2007
    http://www.pavithraravindarexcel.com/2013/12/how-to-split-names-values-by-using_30.html
    watch in youtube How to Split names & values by using the Convert Text to Columns wizard in excel 2007

    ReplyDelete
  3. thank you....it is really helpful.... thank you once again...

    ReplyDelete