In the emotional world, breaking up is hard to do but with MS Excel it is not difficult to break your data up in to two or more columns to make it easier to analyze. You can also combine data pretty easily as you can see from my post entitled “MS Excel Tutorial – Sorting It Out” This blog post and web cast comes from a question on my Facebook Page asking about how to split data in one column into 2 columns. We are starting with an export from a database containing a quantity and an item description in the same column. So the objective is to break that column into 2 columns so that we are left with one column containing the quantity and another with the item descriptions such that they can be sorted. The good news is that our data has a consistent convention defining the point at which we want to split the data in MS Excel into 2 columns. We have a space, dash space ” – “. This is going to make our job very easy.
All we have to do in order to split the column in MS Excel into 2 columns in this case is
- Highlight the entire column
- Go to the ‘Data’ tab
- Click ‘Text To Columns’
- Choose ‘Delimited’ then ‘next’
- Next to ‘Other’ check it off and enter the dash ‘-‘
- Click finish.
This will tell MS Excel to split the column into 2 columns wherever it sees the dash. Then you may want to check your formatting to be sure that the newly formed Quantity column is in fact formatted with a numeric format. Try sorting by Item to be sure that the item column is in fact being read as text. It should be but it never hurts to check! This video will illustrate exactly what I am talking about step by step!