How to split one column into multiple columns in excel

A very common thing that you may need to do in excel is to split the data in one column in excel into multiple columns. An example of this will be being able to split a column containing the full name of students or employees into separate columns of first name and surname.

You may be tempted to type them out or just copy to paste in the respective columns. Even though that looks simpler to do, the question you should ask yourself is, how will you get this done for a spreadsheet with thousands of data or names in it?

For that matter you have to learn how to use the right tool for the right job in excel. In this article, you are going to learn about various effective methods that you can use to split data in a column into various columns.

There are quite a number of methods that you will learn from this article, try and understand them then choose the one that seems easiest to you.

Some of the methods that we will consider in this article include:

1. Using Text to Columns feature
2. Using flash fill
3. Using power query

Let’s take a look at a use case for which these methods will be needed to split one column into multiple ones.

When do you need to split single columns into multiple columns

USE CASE 1:

You have an excel spreadsheet that has a column with the full names of employees in your company but you would want to create a different column for the first name and another column for the last name.

USE CASE 2:

You have an excel spreadsheet that contains student details like their names, student ID, class, date of birth and place of residence. Unfortunately all the data is in just one column and the details for each student is in one cell.

As such you have multiple lines of data in every single cell. You would want to put all these multiple lines in a cell into separate columns.

Either of the two use cases above are very common. These mostly occur when you copy content from a different software like Microsoft Word and paste it in Microsoft Excel. You will therefore learn how to solve both of the use cases stated above using the various methods mentioned earlier.

Method one: Using Text to Columns feature to split a single column into multiple columns

Text to Columns is a feature in excel specifically made to enable you to split text (data) into columns based on a specified separator known as a delimiter (e.g. empty space, comma, semi-colon, etc.) or a fixed width of characters (say you want to separate the text after every 4 characters). 

Using the first use case of having employees’ names in a single column as shown below, let’s split the column into a separate first name and last name column.

If you prefer to learn with videos, then use the video lesson which covers virtually everything mentioned in method one.

To use text to column feature to split the names into separate columns, follow the steps below:

Step 1:

Select the data in the column of interest starting from the first one you want to split (e.g. Select Full names column excluding the heading of the column since you are not going to split the heading)

Step 2:

Click on the data tab

Step 3:

Click on Text to Columns (illustrated in the image below)

NB: In this first use case, you realize that the first and last names are separated by a space to form the full name. Hence, to split them you need to choose space as the delimiter (separator).

On clicking Text to Columns, you get a pop up, the convert text to columns wizard as shown below asking you to choose the file type that best describes your data

Step 4:

Choose Delimited (usually selected by default)

Step 5:

Click on next

Step 6:

Go ahead to choose the delimiter which is a space in this case. Note that you can use multiple delimiters. So, in case some of your text is separated by commas instead of space then you can choose both space and comma. 

Step 7:

Check the data preview to ensure that you are getting them into the number of columns you want. Click on next

Step 8:

On the next screen, you are to set the data format for each column. By default the first column is selected and the data format is set to general. In the case where you are not sure of the data format, go with general. You can however select a specific one like date if the data in that column is a date.

Step 9:

Click on the next column in the data preview box and set the data format for that column as well. Repeat this step for all the columns you have (if you have more than two columns to generate)

The next thing is to set the destination. If you don’t set the destination and go ahead to click on “Finish”, the new columns will replace the old one (the full name column will vanish). Follow the steps below to set your destination.

Step 10:

Click on the upload icon (arrow pointing up) beside the destination textbox. By default the first cell with the full name in it is selected (that’s why you see $A$2 in the destination field)

Step 11:

Click in the actual cell you want the first name to begin from (will change to $B$2 in our example)

Step 12:

Click on the dropdown icon beside the new destination to return to the Text to Columns wizard 

Step 13:

Click on finish to complete the split into separate columns.

Here is the result of all the steps above. As you can see, we have successfully separated one column into multiple columns in excel using the Text to Columns feature.

Now, let’s look at how we can use the flash fill to achieve the same result.

Method Two: How to use Flash Fill to split a column into multiple columns in Excel

Using the sample data of employees’ names used earlier, let’s separate them into multiple columns using Flash Fill. Using flash fill is the easiest method in my opinion and I recommend this method for you if you aren’t familiar with Excel.

Flash Fill is an intelligent feature in Microsoft Excel that determines patterns that were used in solving a particular problem and tries to replicate that pattern to solve other problems. Thus, to be able to use Flash Fill, you would have to provide a sample of the solution you want and it will do the rest for you.

For example, in splitting a column into multiple columns, we will have to do the first one manually then invoke Flash fill to do the rest for us. This means for the first employee, we would have to manually type his/her first name and last name into their respective columns then excel will do that of all the others.

Now follow the steps below to populate the rest of the data using the Flash Fill feature

• Click on the first employees first name 
• Click on Data tab
• Click on Flash fill just beside the Text to Columns icon
• The rest of the first name will automatically populate

You can use the shortcut CTRL + E to invoke the Flash Fill instead of using the above steps. Just make sure you have selected the first name of the first employee and click on the CTRL + E together.

Repeat the steps above for the Last name column (Just select the first employee’s last name and click CTRL + E)

Method Three: How to use power query to split a column into multiple columns

To use the power query tool in excel to split a column into multiple columns, you need to convert the text data in your column into a table range. Follow the steps below to split a column into multiple columns using the power query tool.

Step 1: Select the data in the column of interest (full name column)

Step 2: Click on the data tab

Step 3: Click on from table

Step 4: Check the box beside “My table has headings” if your initial selection included the heading of your column. Otherwise, leave it unchecked.

Step 5: Click OK

Step 6: Click on split columns as shown below

Step 7: Click on By Delimiter

Step 8: Select the delimiter (separator) in your data. Space is selected by default and since it is the separator used in our data, proceed to click on OK.

Step 9: The data has been separated into two columns successfully. Change the name of the column headers by double clicking within them and typing out the name you want.

Step 10: Click on the dropdown icon below Close & Load as shown below

Step 11: Click on the second option (Close & Load To)

Step 12: Select table on the next screen (Load to screen)

Step 13: Choose Existing worksheet

Step 14: Click the table icon just beside the textbox with the table range ($A$1:$A$6) in it.

Note that by default, the initial selection that you started with is what will show. This means if you proceed you replace the initial column with your new column. But that is not what we want to achieve. Hence go ahead and select the new space or columns where you want the data to be placed.

Step 15: Click OK after making your selection. You will be taken back to the previous screen where you chose existing worksheet.

Step 16: Click on Load

The new columns will be successfully inserted into your excel spreadsheet as shown below.

A few clean ups need to be done before saving your document.

Step 17: Select the new columns and data that have been added to your table

Step 18: Click on the design tab

Step 19: Click on convert to Range

Click OK on the pop up to permanently convert the table to a normal range.

You have finally converted a single column to two columns in excel using the Power Query tool. The outcome of the split is shown below: