Microsoft Excel functions banner image.

There are a few super-handy timesaving tricks in Microsoft Excel for when your data is not quite the way you need it to be. The Text to Columns and the Transpose option for the Paste command are two features that could save you from re-typing data that was “incorrectly” captured. Importing and exporting data into and out of Excel, Access and other programmes and databases is a valuable skill and the data manipulation process described below will start you on your way to learning the basics involved.

In this post:

Scenario 1

You may want to “clean” your data before you split it.

I often see learners capture their PAT Phase 2 questionnaire data incorrectly, for example where a question is asked and then the motivation for that answer is required:

Example: Do you like ice-cream? Motivate your answer.

You might end up with data something like the following:

Before you start re-typing all your data, take a look at Excel’s Text to Columns command! This handy wizard walks you through the steps; be sure to keep checking the preview pane to see your progress.

Step 1.1

A delimeter is a separator.

The delimiter is more often than not a comma. That is where CSV files come into play: Comma Separated Values. Excel is normally able to preselect the correct option by inspecting your data.

Step 1.2

Confirm that the delimiter is, indeed, a comma:

That preview looks promising!

Step 1.3

I always recommend ignoring the options in the third and final step as you can do all your formatting once the process is complete.

End result

As easy as that, your data has been divided into two, separate columns.

Add a heading to your new column of comments and your job is done!

Scenario 2

You have a column called “Full name” that contains the name and surname of a list of learners. You need to split the name and surname into two columns, First Name & Surname.

Step 2.1

Insert enough blank columns to the right of the Full name column to accommodate the data that will be “split out” by the Text to Columns command.

Step 2.2

Select all the cells that contain full names, and click on the Text to Columns command. You will now see why Step 2.1 was necessary! You might also see that in the case of a surname comprising of more than one word, for example, van der Merwe.

Step 2.3

Join the parts of the surname together using the CONCATENATE function.

Step 2.4

Use the TRIM function to remove any extra spaces added to the end of some of the surnames (those that do not have multiple “parts”, e.g. “Fox” will now be “Fox “.

Step 2.5

Copy the result of the CONCATENTION functions, the surnames, and paste them as values in the Surname column. Delete all the extra columns (those used to split and then join the parts of the surname).

More about delimiters

delimiter is a sequence of one or more characters for specifying the boundary between separate, independent regions in plain textmathematical expressions or other data streams. An example of a delimiter is the comma character, which acts as a field delimiter in a sequence of comma-separated values. Another example of a delimiter is the time gap used to separate letters and words in the transmission of Morse code.

By MisterFoxOnline

CAT Educator

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.