How to Split Cells in Excel

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 separates a column of cells 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, go 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 leave the data format as ‘General’ because 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 and then click ‘Finish’. If you click ‘Finish’ with the default destination, it will replace the original column. So, we’re choosing B2 as the starting cell.

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, try the below formula.

=LEFT(A2,SEARCH(" ",A2)-1)

This formula uses the SEARCH function to spot the first space character then extracts all the characters to the left with the LEFT function (See the below example).

Then, click on the little green square (fill handle) at the bottom right corner of cell B1 and drag it down over other cells (up to cell B11) to apply the formula to the rest of the cells.

Now, first names are extracted from all the cells.

To extract the last name (Family Name) from the cell A2, type the below formula.

=RIGHT(A2,LEN(A2)-SEARCH(" ",A2))

The provided formula uses the SEARCH function to locate the position of the space character in the string (A2). Then it subtracts the position number of space character from the total length of the name retuend by the LEN function to find the number of characters in the last name. Finally, it extracts the last name by using the RIGHT function.