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.
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.)
Next, we select the data we would like to split out into the new column(s).
On the “Data” tab of the Excel Ribbon, we select “Text to Columns”.
When the Text to Columns Wizard opens, select “Delimited”, then click next.
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.
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.
And we're done:
For the Address Column, we’ll repeat the process using a comma (,) as our delimiter. Voila!
What’s new in Excel 2013?
” 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.
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.
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!
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.