If you are working with data in Excel that you have imported from other sources, sometimes you have to work with data that is not in the format you want. This is especially true for comma-delimited text that is placed in separate cells.
The only way to deal with this data is to split the cell in Excel. This can be done in different ways depending on the data format.
In this article, you will learn how to split a cell, how to expand it to the entire column, and when to select each option.
Convert Text to Columns
One of the most common methods for splitting a cell in Excel is using the Text to Columns tool. This allows you to split an entire column of cells using whichever rules you like.
This feature also includes an easy-to-use wizard, which is why most people prefer to use it. It also handles any text format, be it delimited text – space, tab, or comma.
Let’s see an example of how to use the Convert Text to Columns feature in Excel.
– /
In this example, we want to split the Name column into two cells: the first and last name of the salesperson.
To do this:
1. Select the “Data” menu. Then select Text To Columns from the Data Tools group on the Ribbon.
2. A three-step wizard will open. In the first window, make sure the Delimited option is selected and click Next.
3. In the next wizard window, uncheck the Tab box and make sure Space is selected. Click Next to continue.
4. In the next window, select the “Destination” field. Then, in the spreadsheet, select the cell where you want to put the name. This will update the cell in the Destination field to the location you selected.
5. Now click Finish to complete the wizard.
You will see that the only cell containing both first and last names has been split into two cells containing each one separately.
Note. The above process works because the data to be split in the cell had a space separating the text. This text to column conversion feature can also handle cell splitting in Excel if the text is separated by tab, semicolon, comma, or any other character you specify.
Use the Excel Text Functions
Another way to split a cell in Excel is to use different text functions Text functions allow you to extract portions of a cell that can be output to another cell.
Text functions in Excel include:
- Left (): Extract multiple characters from the left side of text.
- Right (): Extract multiple characters from the right side of the text
- Mid (): Extract a range of characters from the middle of a line.
- Find (): Search for a substring within another string.
- Len (): Returns the total number of characters in a line of text.
You may not need to use all of these features to separate cells. However, there are several ways to use them to achieve the same thing.
For example, you can use the Left and Find function to retrieve the name. The search function helps because it can tell you where the separator character is. In this case, it is a space.
So, the function will look like this:
= LEFT (C3; FIND (“”; C3))
When you press Enter after typing this function, you will see the name being pulled from the string in cell C3.
This works because the Left function requires a number of characters to extract. Since the space is at the end of the name, you can use the FIND function to find a space, which returns the number of characters required to get the name.
You can extract the last name using the Right or Mid function.
To use the Right function:
= RIGHT (C3; LEN (C3) -FIND (”“, C3))
This will extract the last name by finding the position of the space and then subtracting that from the length of the entire string. This gives Right the number of characters it needs to extract the last name.
Technically, you can do the same as the Right function using the Mid function, for example:
= MID (C3; FIND (“”; C3); LEN (C3) -FIND (“”, C3))
In this case, the Find function gives the Mid function as a starting point, and Len combined with Find provides the number of characters to retrieve. The surname will also be returned.
Using Excel Text Functions to Split a Cell in Excel works the same as the Text-To-Column solution, but also allows you to populate the entire column below those results using the same functions.
Split Cell in Excel Using Flash Fill
The last option for splitting a cell in Excel is using the Flash Fill function. To do this, it is necessary that the cells into which you divide the original are next to it.
If so, all you have to do is enter the portion of the original cell that you want to split. Then drag the bottom-right corner of the cell downward to fill the cell below it. When you do, you will see a small cell fill icon with a plus sign next to it.
Select this icon and you will see a pop-up menu. From this menu select “Flash Fill”.
When you do, you will see that Flash Fill will automatically detect why you entered exactly what you entered and will repeat the process in the next cell. It will do this by defining and filling in the name in the original cell on the left.
In fact, you can follow the same procedure when you fill in the entire column. Select the same icon and choose Flashing Fill. It will fill the entire column with the correct name from the cells on the left.
Then you can copy this entire column and paste it into another column and then repeat the same process to extract the last names. Finally, copy and paste the entire column to the desired location in the spreadsheet. Then delete the original column that you used to complete the Flash Fill process.
Split cells in Excel
As you can see, there are several ways to achieve the same. How you break a cell in Excel comes down to where you want to put the end result and what you plan to do with it. Any options work, so pick the one that works best for your situation and use it.
–