How to Split Cells in Excel (separate into multiple columns)

There can be situations when you have to split cells in Excel. These could be when you get the data from a database or you copy it from the internet or get it from a colleague.

A simple example where you need to split cells in Excel is when you have full names and you want to split these into first name and last name.

Or you get address’ and you want to split the address so that you can analyze the cities or the pin code separately.

How to Split Cells in Excel

In this tutorial, you’ll learn how to split cells in Excel using the following techniques:

  • Using the Text to Column feature.
  • Using Excel Text Functions.
  • Using Flash Fill (available in 2013 and 2016).

Let’s begin!

Split Cells in Excel Using Text to Column

Below I have a list of names of some of my favorite fictional characters and I want to split these names into separate cells.:

Dataset that needs to be Split in Excel

Here are the steps to split these names into the first name and the last name:

  • Select the cells in which you have the text that you want to split (in this case A2:A7).
  • Click on the Data tab
  • In the ‘Data Tools’ group, click on ‘Text to Columns’.Click on the Text to Column option
  • In the Convert Text to Columns Wizard:
    • Step 1 of 3 of Text to Columns Wizard: Make sure Delimited is selected (it is the default selection). This would allow you to separate the first name and the last name based on a specified separator (space bar in this case).Click on delimited in the Text to Column wizard
    • Click on Next.
    • Step 2 of 3 Text to Columns Wizard: Select Space as the delimiter and deselect everything else. You can see how your result would look like in the Data preview section of the dialog box.Select the delimiter in the Text to Column Wizard based on which you want to split cells
    • Click on Next.
    • Step 3 of 3 Text to Columns Wizard: In this step, you can specify the data format and where you want the result. I will keep the data format as General as I have text data to split. The default destination is A2 and if you continue with this, it will replace the original data set. If you want to keep the original data intact, select another cell as the destination. In this case, B2 is selected.Specify destination in Text to Column step 3
    • Click on Finish.

This will instantly split the cell’s text into two different columns.

Resulting data where cells have been split into separate columns

Note:

  • Text to Column feature splits the content of the cells based on the delimiter. While this works well if you want to separate the first name and the last name, in the case of first, middle, and last name it will split it into three parts.
  • The result you get from using the Text to Column feature is static. This means that if there are any changes in the original data, you’ll have to repeat the process to get updated results.

Split Cells in Excel Using Text Functions

Excel Text functions are great when you want to slice and dice text strings.

While the Text to Column feature gives a static result, the result that you get from using functions is dynamic and would automatically update when you change the original data.

Splitting Names that have a First Name and Last Name

Suppose you have the same data as shown below:

Split Cells in Excel - Function dataset

Extracting the First Name

To get the first name from this list, use the following formula:

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

This formula would spot the first space character and then return all the text before that space character:

LEFT function to extract the first name

This formula uses the SEARCH function to get the position of the space character. In the case of Bruce Wayne, the space character is in the 6th position. It then extracts all the characters to the left of it by using the LEFT function.

Extracting the Last Name

Similarly, to get the last name, use the following formula:

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

Right function to extract the last name

This formula uses the search function to find the position of the spacebar using the SEARCH function. It then subtracts that number from the total length of the name (that is given by the LEN function). This gives the number of characters in the last name.

This last name is then extracted by using the RIGHT function.

Note: These functions may not work well if you have leading, trailing or double spaces in the names. Click here to learn how to remove leading/trailing/double spaces in Excel.

Splitting Names that have a First Name, Middle Name, and Last Name

There may be cases when you get a combination of names where some names have a middle name as well.

Data set with middle name that needs to be split in Excel

The formula in such cases is a bit complex.

Extracting the First Name

To get the first name:

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

This is the same formula we used when there was no middle name. It simply looks for the first space character and returns all the characters before the space.

Extracting the Middle Name

To get the Middle Name:

=IFERROR(MID(A2,SEARCH(" ",A2)+1,SEARCH(" ",A2,SEARCH(" ",A2)+1)-SEARCH(" ",A2)),"")

MID function starts from the first space character and extracts the middle name by using the difference of the position of the first and the second space character.

In cases there is no middle name, the MID function returns an error. To avoid the error, it is wrapped within the IFERROR function.

Extracting the Last Name

To get the Last Name, use the below formula:

=IF(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))=1,RIGHT(A2,LEN(A2)-SEARCH(" ",A2)),RIGHT(A2,LEN(A2)-SEARCH(" ",A2,SEARCH(" ",A2)+1)))

This formula checks whether there is a middle name or not (by counting the number of space characters). If there is only 1 space character, it simply returns all the text to the right of the space character.

But if there are 2, then it spots the second space character and returns the number of characters after the second space.

Note: These formula works well when you have names that have either the fist name and last name only, or the first, middle, and last name. However, if you have a mix where you have suffixes or salutations, then you’ll have to modify the formulas further.

Split Cells in Excel Using Flash Fill

Flash Fill is a new feature introduced in Excel 2013.

It could be really handy when you have a pattern and you want to quickly extract a part of it.

For example, let’s take the first name and the last name data:

Data that needs to be split in Excel

Flash fill works by identifying patterns and replicating it for all the other cells.

Here is how you can extract the first name from the list using Flash Fill:

  • In cell B2, enter the first name for Bruce Wayne (i.e., Bruce).Enter the first expected result in the adjacent cell
  • With the cell selected, you’ll notice a small square at the right end of the cell selection. Double click on it. This will fill the same name in all the cells.Use the Autofill handle to get the result in all the cells
  • When the cells are filled, at the bottom right you’ll see the Autofill Options icon. Click on it.Click on the autofill options icon
  • Select Flash Fill from the list.Select the Flash Fill option
  • As soon as you select Flash Fill, you’ll notice that all the cells update itself and now show the first name for each name.

How Flash Fill Works?

Flash Fill looks for the patterns in the data set and replicates the pattern.

Flash Fill is a surprisingly smart feature and works as expected in most of the cases. But it also fails in some cases too.

For example, if I have a list of names that has a combination of names with some having a middle name and some don’t.

If I extract the middle name in such a case, Flash Fill will erroneously return the last name in case there is no first name.

How to Split Cells in Excel - Flash Fill error

To be honest, that’s still a good approximation of the trend. However, it is not what I wanted.

But it still is a good enough tool to keep in your arsenal and use whenever the need arises.

Here is another example where Flash Fill works brilliantly.

I have a set of addresses from which I want to quickly extract the city.

Using flash fill to split cells in Excel - address dataset

To quickly get the city, enter the city name for the first address  (enter London in cell B2 in this example) and use the autofill to fill all the cells. Now use Flash Fill and will instantly give you the name of the city from each address.

Similarly, you can split the address and extract any part of the address.

Note that this would need the address to be a homogenous data set with the same delimiter (comma in this case).

In case you try and use Flash Fill when there is no pattern, it will show you an error as shown below:

Flash fill error message

In this tutorial, I have covered three different ways to split cells in Excel into multiple columns (using Text to Columns, formulas, and Flash Fill)

Hope you found this Excel tutorial useful.

You May Also Like the Following Excel Tutorials:

  • With OneDrive Excel (2020) there is no Data – Text to columns. There is now; on the Data page – Flash Fill
    On a couple of rows, you set out how you’d like the data to display across your columns. Highlight each column in turn and click on Flash Fill
    (ensure that there are no spaces between the rows that you want the information to flash fill in)

  • Flash Fill is the best….

  • Bhaskar Praveen M says:

    the destination change is a good tip.. i used to copy the contents into a different column and perfrom text to columns. with this tip, can eliminate one step… thanks Sumit.. keep these coming.

  • Francis D'Costa says:

    Great idea. Thanks Sumit.
    Francis D’Costa, Canada

  • >
    shares