Learn how to split cells in Excel into multiple columns using Text to Columns feature, Flash Fill, or Excel formulas.
Suppose you have a long column of full names and you want to split those names into first name and last name. But you don’t have time to type each and every name into two different columns. That’s where Excel’s Split cells features come in.
In Excel, you can split the data in a cell into two or more cells using the Text to Columns feature, Flash Fill, or Formulas. In this post, we will discuss how you can split the cells hassle-free.
Split Cells in Excel using Text to Columns Feature
Text to Columns is an advanced tool that will take a column of cells and separate them into two or more adjacent cells based on a character that you specify. It can spit a cell with different parameters such as space, commas semicolon, tab, or any other character.
To split the cells in Excel, first select a cell or a range of cells that you want to split. We’ll use the following example to show you how to split cells.
Once you select your cell, head to the Data tab. In the ‘Data’ tab, click on the ‘Text to Columns’ icon in the ‘Data Tools’ group.
Once you click on that, a Text to Columns wizard will appear. In that, select your data type. The ‘Delimited’ option is the default selection. Since our data already has a space character, we choose ‘Delimited’ and click ‘Next’.
In Step 2 of 3 of the wizard, check the ‘Space’ checkbox and uncheck the rest under Delimiters. If your data contains a comma, semicolon, or any other separators, then you can choose them as delimiters. Since our data contains only ‘space’, we choose the ‘Space’ delimiter. Then, click ‘Next’.
In Step 3 of 3 of the wizard, choose ‘General’ as Column data format. We will keep the data format as General as we have text data to split.
You can also change the destination of where you want your split data to be displayed in your spreadsheet. If you continue with the default destination, it will replace the original data set. So, we’re choosing B2 as the starting cell. And click ‘Next’.
Also, in the ‘Data preview’ box, you can see how your split data will look like.
As you can see, the Names column is split into ‘First Name’ and ‘House Name’ columns.
Split Cells in Excel using Flash Fill Feature
The Flash Fill method is the quickest and easiest method for splitting cells. Flash fill recognizes patterns and replicates them for all the other cells.
For example, type Eddard (First Name) in the adjacent column of the original data.
Now, go to the ‘Data’ tab and click on the ‘Flash Fill’ option next to the ‘Text to Columns’ feature.
Once you click the ‘Flash Fill’ option, first names from every cell will be extracted into the adjacent column. Now, the first name for each name is extracted into column B.
Do the same thing for the ‘Family Name’ as well. Remember, Flash Fill in Excel only works when it recognizes a pattern in the data.
Split Cells in Excel using Formulas
Some people may not prefer to split cells using formulas in Excel because it requires you to remember the formulas. But compared to the other two methods, formulas have the advantage, because they produce dynamic results. When you change the original data, the results would automatically update.
To extract the First Name from the cell A2, use the following formula.
This formula uses the SEARCH function to spot the first space character then extracts all the characters to the left of it by using the LEFT function (See the below example).
Then, click on the little green square at the bottom right corner of cell B1 and drag it down over to cell B11 to apply the formula to the rest of the cells.
Now, first name are extracted from all the cells.
To extract the last name (Family Name) from the cell A2, use the following formula.
This formula uses the search function to find the position of the spacebar using the SEARCH function. Then it subtracts the spacebar’s position number from the total length of the name using the LEN function. This gives the number of characters in the last name. Finally, it extracts the last name by using the RIGHT function.
Then, you can drag and apply the formula to cell C2:C11, and you will get the following result.
That’s it. Now, you have learned how to split a cell into multiple columns. Hope you found this Excel tutorial useful.