Mar 27, 2014

Have you ever needed to sort or filter data in Microsoft Excel but couldn’t because the data have been combined into a single column rather than multiple columns? A common example is postal addresses, where you may need to sort or filter by state, city or postcode. This generally happens because you have opened or imported the file that has been created in another application. To properly sort or filter the data, you’ll need to convert the text into columns and this can be easily done in Microsoft Excel 2007, 2010 and 2013. Follow the simple steps below.

1. Select the range of data you wish to split.
2. Under the ‘Data’ tab, click the ‘Data Tools’ and select the ‘Text to Columns’ button.
   
3. In the dialog box, there will be two options under the heading ‘Original data type’ heading – ‘Delimited’ and ‘Fixed width.’ Select the option that best suits with your selected data. Generally, the delimited option is used so let’s choose this option.
   • Delimited is used when there is a character that separates each group of words e.g. a comma, tab, or space.
   • Fixed-width is used where each group is a set number of characters.
4. Click the ‘Next’ button.
5. Under the heading ‘Delimiters,’ tick all the delimiters that apply to your data. If the data has delimiters not listed, tick ‘Other’ and type the symbol used in the selected data.
6. Click the ‘Next’ button.
7. In this next step, you are able to format the columns of data. For example, if you have a column with numbers and the numbers start with zero (e.g. a mobile number or phone number prefix), you will have to format it as text; otherwise the zero will not appear in your worksheet.
8. Once done, click ‘Finish.’

You’ll notice that you data is now split into its components, and you’ll now be able to sort or filter your data according to your needs. For more great tips and trick using Microsoft Excel, I would recommend one of the courses delivered by New Horizons. Our dedicated staff and trainers will be more than happy to help you.