Sometimes you will inherit data from a tool or your boss that needs to be separated into each column to make the data more accessible 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 more comfortable. Luckily, we can use the text to column section within excel to quickly break this out then manually doing this. Here are the steps on how to use the converted version to columns wizard in Excel. I’ve also included pictures that help explain these actions in greater detail. Let’s begin!
Highlight the Data You want to Edit
You want to make sure you highlight the data that you 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 publish, 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 choose everything after the first cell that you are trying to split. Once all of that is selected, you can press 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 select multiple settings to specify to edit. To do this, you merely need to check off the boxes. As a note, Excel will 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 explicitly call out the comma and 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 not to specify the place, you can run into the risk of replacing valuable data that you will need. Since I knew 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 so that you don’t have the repeat values.
Text to Columns is an excellent 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 of 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 an extended date that needs to be broken down easier. Here are the steps on how to break out a time with text to columns. It’s 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 its own column, so it is always best to spot check this and make any changes that you need to afterward.
If you are looking for a good Excel plugin that goes with this tutorial, you can check out Kutools.