[Quick Tip] How to Split Cells in Excel

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:

How to Split Cells in Excel - Text to Column Data

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

  • Select the cells (in this case A2:A7).
  • Go to Data –> Data Tools –> Text to Columns.How to Split Cells in Excel - Text to Column select
  • In the Convert Text to Columns Wizard:
    • Step 1 of 3: 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).How to Split Cells in Excel - Text to Column delemited
    • Click on Next.
    • Step 2 of 3: Select Space as the delimiter and deselect everything else.How to Split Cells in Excel - Text to Column step 2
    • Click on Next.
    • Step 3 of 3: In this step, you can specify where you want the result. 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.How to Split Cells in Excel - Text to Column step 3
    • Click on Finish.

This will instantly split the names into first name and second name.

How to Split Cells in Excel - Text to Column result

Note:

  • Text to Column feature splits the content of the cells based on the delimiter. While this works well when you have a 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 Text to Column feature gives a static result, the result that you get from using functions are 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:

How to 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:

How to Split Cells in Excel - left function

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 the 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))

How to Split Cells in Excel - right function

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.

How to Split Cells in Excel - middle name

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:

How to Split Cells in Excel - Function dataset

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).How to Split Cells in Excel - Flash Fill Bruce
  • 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.How to Split Cells in Excel - autofill handle
  • When the cells are filled, at the bottom right you’ll see the Autofill Options icon. Click on it.How to Split Cells in Excel - autofill options
  • Select Flash Fill from the list.How to Split Cells in Excel - select flashfill
  • 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 surprisingly smart in a lot of cases, but it also fails in some cases. 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 address from which I want to quickly extract the city.

How to Split Cells in Excel - flash fill address

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 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:

How to Split Cells in Excel - flash fill error message

You May Also Like the Following Excel Tutorials:

  • Shehzad Siddiqui says:

    http://tutorialway.com/microsoft-excel-font-formatting/
    check this for excel

  • Deepak says:

    Flash Fill is the best….

  • MF says:

    To extract the last name (last word), we may use this shorter formula.
    =TRIM(RIGHT(SUBSTITUTE(A2,” “,REPT(” “,10)),10))
    To make it more robust, change 10 to LEN(A2)
    Explanation can be found at the blogpost below:
    https://wmfexcel.com/2014/11/17/extract-last-word-from-a-text-string/

  • 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

    • Sumit Bansal says:

      Thanks for commenting Francis.. Glad you liked it.

    • Sumit Bansal says:

      Thanks for commenting Francis.. Glad you liked it.

  • >