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 Columns 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.:
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’. 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 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. 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. Click on Finish.
This will instantly split the cell’s text into two different 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:
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 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:
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: 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: 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 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.
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.
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:
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.
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 Separate Cells/Columns.
