Sometimes you will inherit data from a tool or your boss that needs to be separated into each column to make the data easier to sort and process. One example of needing to do this is if you have imported data from the web into excel and the name of the football player, their team, and their place are all put into the same cell. While you can import that data, you now need to split that results to view it easier. Luckily, we can use the text to column section within excel to quickly break this out than manually doing this. Here are the steps on how to use the convert text to columns wizard in Excel. I’ve also included pictures that help explain these steps in greater detail. Let’s begin!
Highlight the Data You want to Edit
You want to make sure you highlight the data that you actually want to separate with the text to column feature. You might have headings above the column that you don’t want to edit, so make sure you are just selecting the data that makes sense to edit so you don’t screw up the rest of the document. To quickly select the data, you can use the CTRL + SHIFT + Down command to select everythnig after the first cell that you are trying to split. Once all of that is selected, you can press on the up arrow to go back to the top of the selection to make sure everything is still highlighted to edit.
Text To Columns Section
Once the data is selected, you can head over to the data and text to column section to bring up the menu.
Specify if there are any Delimiters
In this example below, I have spaces and I have commas that separate the data into this cell, so I will need to choose the delimited section. In excel, I can choose multiple settings to specify to edit. To do this, you simply need to check off the boxes. As a note, excel will actually give you a preview of what this change will do if you go ahead with it. You can also choose the fixed width section if everything is broken out by spaces in the column you want to break out. In this example below though, I needed in step 1 to choose delimited to specifically call out the comma and the space from cell A3 and down.
Choose the destination for the New Columns
Once the data has been selected and the correct delimiters have been added, you can then specify where you want the information to fall in. This is a good idea to do just to make sure you know exactly where this data is going. If you choose to not specify the place, you can run into the risk of replacing valuable data that you will need. Since I new I wanted to break out the first name, the last name, the team, and the position, I created a few extra columns after column A. You will also notice that I specified that I want the data to start in B3.
Once all the steps have been added, you can then click on the finish button and the data will then present itself. Once this is the case, you can cut the original column just so you don’t have the repeat values.
Text to Columns is a great feature in excel that will save you a lot of time and effort if you need to separate out the data. If you are doing this process manually, you are wasting valuable time. Another neat feature in this tool is that you can specify “other” when you choose what the separation should be. If you have something else like an asterisk or a pipe that splits the name, you can specify that in this tool with the other section. You can also use this tool if you have a long date that needs to be broken down easier. Here are the steps on how to break out a date with text to columns. It’s basically the same steps as above with selecting the data you want and choosing the type of date that the numbers need to be broken out into. You should also consider doing these steps more than once if the data comes back slightly different than what you want. An example of this could be if you have someone that has a longer name than just a first and last name. The text to column feature will break all of this out into it’s own column, so it is always best to spot check this and make any changes that you need to afterwards.
If you are looking for a good Excel plugin that goes with this tutorial, you can check out Kutools.