Microsoft Excel formulas menu

There are a few super-handy timesaving tricks in 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

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 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

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 2

Confirm that the delimiter is, indeed, a comma:

That preview looks promising!

Step 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!

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.

https://en.wikipedia.org/wiki/Delimiter

By MisterFoxOnline

CAT Educator

Leave a Reply

Your email address will not be published. Required fields are marked *

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