≡ Menu

How to split Excel data in one column into separate columns

Hi, Tim here again with a final Microsoft Excel tip for this year. This smarter working with Excel tip is about splitting comprehensive data in one cell into a number of discrete cells so you can manage the information easier within your CRM system. I use it when I get a column containing a complete address that I want to split out, or when I get a full name and email address in one column. Here’s how I do it….

  1. If necessary add a new blank column(s) to the right of the fields you want to split out. Do this by Right-Clicking on the header of the column to the right and select and click ‘Insert’ Make sure you create enough new columns, especially if you are splitting out a full address onto different cells for insertion into a CRM system.
  2. Next, you must set the Delimiter properties of the column you want to split out. This will depend upon how the data appears in the column. Note that you can select more than one delimiter – very useful if the data is separated differently within the column (some people are so messy!)
  3. Often, especially with address data, components of the address are delimited by a comma between the words as in this Microsoft address: Microsoft Campus, Thames Valley Park, Reading, West Berkshire RG6 1WG Other data may just contain spaces between the words as in Richard Maybury Richardm@prioritymanagement.com
  4. Once you have identified how the words are separated (Delimited) you now need to set that up in Excel. Click on the column header, hit the Data tab in the Top toolbar / Ribbon and click on Text To Columns button
  5. In the Convert Text to Columns wizard select Delimited then hit the Next button
  6. Select the appropriate DelimiterComma as in the address example above and/or Space in the second example, Hit Next
  7. In step 3 of the Convert text to columns wizard accept the General default and hit Finish
  8. You now have data columns you can map to your CRM system or use directly for marketing purposes.

You can see my tip for combining 2 or more columns of Excel data into a new column here and see how I  convert a column of email address data into formatted email addresses to insert into an Outlook Email here

Get exclusive free productivity resources and tips now
Email:

 

{ 2 comments… add one }
  • Richard Maybury March 11, 2014, 7:51 am

    You are most welcome Solomon

  • Solomon Daniels March 11, 2014, 5:52 am

    Found this tip to be very helpful. This project has been upcoming and I’ve been worried about exactly how I was going to be able to sort through email addresses and separate retailers from suppliers. Thanks!

Leave a Comment

I love sharing with humans! *