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).
Split Cells in Excel Using Text to Column
Below I have a list of names of some of my favorite fictional characters:
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.
- 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).
- Click on Next.
- Step 2 of 3: Select Space as the delimiter and deselect everything else.
- 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.
- Click on Finish.
This will instantly split the names into first name and second name.
- 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:
Extracting the First Name
To get the first name from this list, use the following formula:
This formula would spot the first space character and then return all the text before that space character:
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:
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.
The formula in such cases is a bit complex.
Extracting the First Name
To get the first name:
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:
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).
- 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.
- When the cells are filled, at the bottom right you’ll see the Autofill Options icon. Click on it.
- Select Flash Fill from the list.
- 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.
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.
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:
You May Also Like the Following Excel Tutorials:
- How to Quickly Combine Cells in Excel.
- How to Extract a Substring in Excel Using Formulas.
- How to Count Cells that Contain Text Strings.
- Extract Usernames from Email Ids in Excel [2 Methods].
- How to Split Multiple Lines in a Cell into a Separate Cells/Columns.