There will be times where you are collecting, cleaning, or otherwise manipulating data from external sources. Moments where you will need to extract and separate values from an obscenely long string value, or simply separate numerical data which was bludgeoned together into one cell by the software you exported data from, and place them in individual columns in order for them to have any form of significance.

This is where Excel’s long standing Text To Columns tool comes in handy.

The tool gives you two ways to split long, unruly, or more specific data fields into as many or as few sections as you want. Either with a delimiter such as a tab, comma, semicolon, or your personally specified character, or using a fixed width (number of characters) between each division.

Something like splitting area codes from phone numbers, or isolating address numbers from street names can benefit from the batched nature of the Text To Columns tool. 

The best part about it is… Because it’s an Excel tool if you forget a step, or it doesn’t output as you would have liked, you can undo it without harming any of your other data!

Without further ado: Here is a step by step guide to using Text To Columns.

Step 1: Selecting your range.

• If you already have the headers and blank spaces ready for your formatted data, I would suggest to select only the data which is to be split.

• If you are starting your analysis with this step, or have the unformatted data in another sheet from your main analysis, then feel free to select the entire column range to avoid missing any rows.

Step 2: Initiating the Text To Columns Wizard

• Located in the Data tab, under the Data Tools group, you will find the Text To Columns tool.

Step 3: Choosing a split type based on your needs

• Delimited: splitting your selected range based on the occurrence of a specified character of your choosing.

• Fixed width: splitting your selected range based on a number of characters in the cell. Not all cells need to have the same total amount of characters, but splits will occur from left to right based on the number of characters defined in each split.

Step 4: Formatting your splits, and where they will go

• You now have the option to review each split you will be making, and assign a format to them en-mass to avoid losing any valuable information in the transformation.

• This includes the option to completely omit a specific section if you know it contains junk data

• There is also the destination field, which allows you to specify where you want the output of the Text To Columns Wizard to be, by default it will overwrite the existing data and start its splits in the initial range.

  • I would recommend where possible to export the data from the wizard to a range where it will not overwrite any information.

Step 5: Finish the transformation

• At this point we can click on the finish button and validate that the data was split correctly, and that there were no exceptions in the data which may have caused unsightly issues.

• Remember that once you save or close the document you will not longer be able to undo these changes! If you are not sure you like the results of the split, undo the changes before going any further.

Thanks for reading!