3 Ways to Split a Cell in Excel

When you work in Excel with data that you have imported from other sources, you sometimes have to work with data that is not in the format you want. This is especially true for comma-separated text that comes in separate cells.

The only way to deal with that data is to split a cell in Excel. There are several ways to do this, depending on the format of the data.

In this article, you’ll learn how to split a cell, roll it out to an entire column, and when to choose each option.

Convert text to columns

One of the most common methods of splitting a cell in Excel is using the Text to Columns tool. It lets you split an entire column of cells using whatever rules you want.

The feature also includes an easy-to-use wizard, which is why most people prefer to use it. It also handles any text format, whether the separator text is a space, a tab, or a comma.

Let’s look at an example of using the Text to Columns function in Excel.

In this example, we want to split the Name column into two cells, the seller’s first name and last name.

To do this:

1. Select the Facts menu. Then select Text to Columns in the Data Tools group on the ribbon.

2. This will open a three step wizard. Make sure that in the first window: limited is selected and select Next one

3. In the next Wizard window, deselect tab and make sure Room is selected. Select Next one Get on.

4. In the next window, select the Destination field. Then, in the worksheet, select the cell where you want to place the first name. This will update the cell in the Destination field to where you selected.

5. Now select Finish to complete the wizard.

You will see that the single cell that contained both the first name and the last name has been split into two cells containing each separately.

Remark: The above process works because the data to be split in the cell had a space separating the text. This text-to-column function can also handle splitting a cell in Excel if the text is separated by a tab, semicolon, comma, or any other character you specify.

Using Excel Text Functions

Another way to split a cell in Excel is to use various text functions. Text functions let you extract bits of a cell that you can output to another cell.

Text functions in Excel include:

  • Left(): extract some characters from the left side of the text
  • Right(): extract some characters from the right side of the text
  • Mid(): Extract some characters from the middle of a string
  • Find(): Find a substring within another string
  • Len(): Returns the total number of characters in a text string

If you want to split cells, you may not need to use all of these functions. However, there are multiple ways you can use it to accomplish the same thing.

For example, you can use the Links and Search function to extract the first name. The search function helps because it can tell you where the separator is. In this case it is a space.

So the function looks like this:

=LEFT(C3,FIND(” “,C3))

If you press enter after typing this function, you will see that the first name is extracted from the string in cell C3.

This works, because the Links function needs the number of characters to extract. Since the space character is at the end of the first name, you can use the FIND function to find the space, which returns the number of characters you need to get the first name.

You can extract the last name using the Right function or the Center function.

To use the Right function:

=RIGHT(C3,LEN(C3)-FIND(” “,C3))

Extracts the last name by finding the position of the space and then subtracting it from the length of the total string. This gives the Right function the number of characters needed to extract the last name.

Technically, you could do the same thing as the Right function using the Mid function, like this:

=MID(C3,FIND(” “,C3),LEN(C3)-FIND(” “,C3))

In this case, the Find function returns the Mid function as the starting point, and the Len combined with Find returns the number of characters to extract. This also returns the last name.

Using Excel text functions to split a cell in Excel works just as well as the text-to-column solution, but you can also fill the entire column below those results with the same functions.

Split Cell in Excel with Flash Fill

The last option to split a cell in Excel is to use the Flash Fill function. This requires the cells you are splitting the original into to be right next to it.

If so, all you need to do is type in the part of the original cell you want to split. Then drag the bottom right corner of the cell down to fill the cell below. When you do this, you will see a small cell fill icon appear with a small plus sign next to it.

Select this icon and you will see a pop-up menu. Select Flash Fill in this menu.

When you do this, you will see that the Flash Fill function will automatically detect why you typed what you typed, and will repeat the process in the next cell. It does this by detecting and filling in the first name in the original cell on the left.

You can actually do the same procedure if you fill the entire column. Select the same icon and select Flash Fill† It fills the entire column with the correct first name from the cells on the left.

You can then copy and paste this entire column into another column, then repeat the same process to extract the last names. Finally, copy and paste that entire column where you want it in the spreadsheet. Then delete the original column you used to run the Flash Fill process.

Split cells in Excel

As you can see, there are a few ways to accomplish the same thing. How you split a cell in Excel comes down to where you want the final result and what you want to do with it. All options work, so pick the one that makes the most sense for your situation and use it.

Leave a Reply

Your email address will not be published.