Yawn-proof software training

Splitting or Separating Data in Columns - And a new Quick Tool in Excel 2013

My clients come to me with a variety of questions when it comes to their software and files. One of the more common requests I get is for “cleaning” data files that need to be sorted and formatted for import to a given program. They will supply me with contact lists where names and addresses are mixed up, variations and price lists with inconsistent naming formats, and so on, that need to be sorted an refined for more versatility.

The most common format for transferring/importing/exporting data is in a Comma Separated Values (CSV) file which are easily created in Microsoft Excel. These files aren’t very pretty to look at, but their lack of “frills” makes them versatile for crossing platforms – address books, database software, website content management systems, etc.

For today, we’re using sample contact details that need to be split out into more specific fields. You’ll notice in our example that the contact’s full name, and address are in a single column for each.  We’re going to break these out into first and last names, and specific street, city and province address details.
 Excel 2013-Text-to-Columns-Sample-Data

First, we’ll need to make some room in the file by adding new columns for each of the new fields we want to create.  (Right-click the top of the column next to where you’d like to insert a new column, click Insert, and a new column will appear to the left of the selected column.)
 Excel 2013-Insert-column

Next, we select the data we would like to split out into the new column(s).
 Excel 2013-Text-to-Columns-start

On the “Data” tab of the Excel Ribbon, we select “Text to Columns”.
 Excel-2013-Text-to-Columns---Data-Ribbon

When the Text to Columns Wizard opens, select “Delimited”, then click next.
 Excel 2013-Text-to-Columns---step-1

Choose your preferred delimiter to separate the data. In this example, we’re using a “space”. You can see an example of the separation in the Wizard preview pane.

Excel 2013-Text-to-Columns---step-2
 
In Step 3 of the Wizard, you can specify formatting for the data that is separated. We have no special requests here, so we’ll click Finish.
 Excel 2013-Text-to-Columns---step-3
And we're done:

Excel 2013-Text-to-Columns-complete

For the Address Column, we’ll repeat the process using a comma (,) as our delimiter. Voila!
 Excel 2013-Text-to-Columns---comma

 What’s new in Excel 2013?

Flash Fill” was introduced in the latest release of Microsoft Excel. Using this tool, Excel “picks up” patterns in your data entry, then repeats the process for remaining data.

 
Excel 2013-Text-to-Columns-start
                                                                                         

Here, I started entering the last name of our contacts in the new column, with two names entered, I clicked the “Flash Fill” option from the Fill section of the Home Ribbon.  Excel then automatically populated the remaining data.
 Excel 2013-Flash-Fill

This works with any of the delimiters (comma, space, hyphen, etc.) that you may select in the Text to Columns wizard. What a speedy trick!

Excel 2013-Flash-Fill-Complete
Outlook 2010 or 2013 - Conversation CleanUp
What’s the password?

Related Posts

 

Comments

No comments made yet. Be the first to submit a comment
Already Registered? Login Here
Guest
Friday, 18 October 2019

Captcha Image

Book

How To Choose a Software Trainer They'll Thank You For

Survey says: "Sitting through software training is painful".

Knowing how to use MS Office and knowing how to train people to use it are two different things. Download this Special Report and discover how to choose a trainer that leaves your employees feeling useful.

  • Claire Langfield, Executive Assistant

    Chippewas of Rama First Nation

    The majority of my computer training focused on Publisher. I have received positive comments and feedback from several of my colleagues on the changes between my first attempt and current expertise creating the monthly internal newsletter for my department. They enjoy the updated format and the way in which the information is presented. 

    Deborah assisted me through a learning curve, as I had not previously had any exposure to this program prior to joining Rama. She also provided me with a refresher course on PowerPoint and Excel.  Deborah is a patient teacher and tailored the training to suit my job requirements.