Text to Columns

If you deal in any kind of exported data, this will probably come in very useful.
Text to columns is a very simple and powerful part of excel that will allow you to keep your data clean, and potentially more useful. There are many reasons you might need to parse your spreadsheet, the most common I have seen is with names and addresses.
For most purposes, to use text to columns successfully there will need to be a character that we can use as a delimiter (the point where we will make the split). You can also use fixed width if your column is full of regimented data, I will be assuming you have a delimiter for this post.

To prepare your sheet you have to be aware of a couple of important points;

• The data will be split at each delimiter it finds, be sure that you know what effect it will have before you run it.
• Text that is split will be entered into the next column if there are several delimiters they will be entered over the appropriate number of columns.

First, identify what you will use as the delimiter, it might be a comma,  space, semicolon or tab as standard, you also have the option to use a custom character. With a custom character, it can only be a single character that you use.

Top tip: If you want to split on a word or phrase, use find and replace to swap your string for a character that you don’t use elsewhere. I use the | (pipe) because I rarely have a use for it anywhere other than here, and there is a humorous paradox here as its main purpose in programming is to join things.

Select the column you want to separate, then move up to the Data tab and select Text to Columns to open the wizard. Select Delimited and then Next to continue.

Make sure the delimiter you want to use is selected and the others are not, enter your custom delimiter if required, a preview of your results will be shown below. When you are ready, chose Next.

Here you can select each column that will be created and set the format of them. You can also set the option to skip the column if you don’t need it. If needed you can choose for the results to be entered into a location that is not the adjacent column by selecting the area to use by clicking the range selector on the right-hand side of ‘Destination’.

Press Finish and you will be taken back to your sheet, with all the changes made.

Top tip 2: If you have dates in American format MM/DD/YYYY and you want to convert them to DD/MM/YYYY (or vice versa), use Text to Columns without any delimiters, change the column format to Date and select what you desire. Press Finish and you will have what can be a very complicated process completed very easily.

Find more quick tips like this and more on my post: Top skills to make you look like a wizard!

The Excel cheat sheet mugs below will give you a head start to becoming a Spreadsheet wizard!