How to Split Cells in Excel

This article explains how to split a cell in Excel into individual cells.

How Do I Split a Cell Into Multiple Cells?

There are several ways to split a cell into multiple cells, depending on the content of the cell you want to split.

Split a Cell With Convert Text to Columns

You need to determinate a pattern in order to split a cell. This pattern would be some delimiter like a comma, semicolon, or colon.

1. In the example below, you can see the cell contains a semicolon between each piece of information. This semicolon lets you split those individual elements into other cells.

   

2. Select the cells you want to split. Select Data from the menu and select Text to Columns from the ribbon.

   

3. Select the Delimited radio button from the pop-up window and select the Next button.

   

4. Select the appropriate delimiter character (in this example, semicolon), and select the Next button. You will see a preview of what the output cells will look like.

   

   If none of the listed delimiters works for your situation, you can select Other and type the delimiter in the text box. Also if your delimiter character is in multiples (like spaces), you can select the checkbox next to Treat consecutive delimiters as one.

5. In this final window, you can choose the format for your output cells, as well as the destination for your newly split cells. Select the Finish button when you're done.

   

6. Finally, you'll see the results in your main spreadsheet. If you've set up everything right, your original cell will split perfectly across multiple cells.

   

Split a Cell With Excel Functions

You can do the same thing using Excel functions. This approach is best if the cell only contains two parts you need to split. The benefit is using a function is much faster than the previous method.

1. In this example, to split out the left side of the information, you'll need to use Excel's LEFT function. Place the cursor in the cell where you want that information to go and type =LEFT(A1,FIND(";",A1)-1). Press Enter.

   

   Replace "A1" in the example here with the source cell you want to split.

2. Place the cursor into the next output cell and use Excel's RIGHT function to extract the right side of the source string. To do this, type =RIGHT(A1,LEN(A1)-FIND(";",A1)). Press Enter to finish.

   

3. When you're done, your first cell will be split into two. Fill each of these down to split the rest of the cells. Hold the Shift key down and place the cursor at the lower right corner of the cell until it changes into two lines with an arrow above and below. Double-click the mouse to fill down. Repeat this with both columns.

   

Split Cells Using Flash Fill

Flash Fill in Excel is a very convenient feature which will magically figure out the delimiter based on an example you type in adjacent cells.

1. In the first cell next to your original cell you want to split, type the first segment of the cell. Then select that cell and press CTRL + E. Excel automatically recognizes what delimiter you're using based on your example and will finish splitting the rest of the cells below it for you.

   

2. Repeat this same process with the other sections you want to split and use Flash Fill to split out the cells underneath it.

   

Split One Cell Across Multiple Adjacent Cells

If you'd like to make one cell span across several cells next to it, there is a simple trick to do this.

1. Select the cell and all cells next to it you'd like to span across multiple cells below (or beside) it.

   

2. Select Home in the menu and then select Merge & Center from the ribbon. When you're done, all selected cells will merge into one and span across multiple cells next to it.