Microsoft Excel is a great tool for working with data of all types, including text. You can import or paste data from just about anywhere in just about any format, which can lead to the need to split cell contents. For instance, you may have tables where an entire record – with field delimiters – is contained in a single cell. Perhaps you have cells with the full names of customers, but you need to split the first and last names. Regardless of the reason, Microsoft Excel has multiple solutions.
In this guide, you will learn six different methods of splitting cells in Microsoft Excel. First, you will learn how to use the text-to-columns wizard to specify exactly how you want the contents split. Second, you will learn to use the flash fill tool to split cell contents based on patterns. Third, you will learn how to use a built-in text function so the split cells will remain updated: TEXTSPLIT. Fourth, you will learn how to use Power Query to split cells and how to split them into rows instead of columns. Finally, you will also learn how to split merged cells.
Split cells using Text to Columns
If you need to split the text in a cell into several cells, you can use the text-to-columns tool. The wizard will guide you through the process, and you can specify whether the contents are separated by a specific delimiter or have a fixed width.
Follow the steps below to use the text-to-columns tool to split full names into first and last names.
1. Select the cells containing the full names. On the Data tab, click the ‘Text to columns’ button.
2. In this case, the names are separated by a space, so you should choose ‘Delimited’ and click ‘Next’.
3. Select the delimiter from those available or choose ‘Other’ and type it into the text box. Click ‘Next’ to specify the destination and data types. If you want to replace the existing cells, click ‘Finish’.
4. Select the destination cell and click ‘Finish’.
5. The first and last names are now in separate cells.
Split cells using Flash Fill
If you need to split the contents of a cell, you can use the Flash Fill tool. In fact, you can also use it to extract part of the contents. For simple patterns, providing a single sample cell is enough for Microsoft Excel to recognize the pattern, while more complex patterns may need two or three.
1. Open the spreadsheet containing the cells you want to split. In the column to the right, reproduce the contents of the corresponding cell to the left up to the point where you want to split.
2. Grab the fill handle and drag it down to the last cell. Click the button in the bottom right corner and select ‘Flash fill’.
3. As you can see, Excel has interpreted the pattern correctly. Repeat steps 1 and 2 for the last name.
Split cells using TEXTSPLIT
In the examples below, you will learn how to use the TEXTSPLIT function to split the contents of a cell. One of the benefits of using formulas is that the split cells will update when the original data changes.
Follow the steps below to split a full address delimited by commas into separate cells.
1. Type the TEXTSPLIT function into an empty cell.
2. Click the cell that contains the text you want to split, then type a comma.
3. Type the delimiter between quotation marks. In this case, the text is delimited by commas.
4. Close the parenthesis and press ‘Enter’. Drag the fill handle to copy the formula to the last row.
Split cells into columns or rows using Power Query
Power Query is a very versatile tool that can be used to manipulate data in any way you want. This includes splitting the contents of cells into separate columns or even separate rows. Follow the steps below to split cell contents using Power Query.
1. Select the cells you want to split and go to the Data tab to access Power Query. If you’re using a Mac, copy the table and launch Power Query, as shown below.
2. Click ‘Enter data’.
3. Paste the table contents using the Cmd + V shortcut. Click ‘OK’ to move to the next window.
4. Click ‘Transform’, then ‘Split column’ to see the options. In this case, the text is separated by delimiters.
5. If you want to split the contents to separate columns, select the separator and click ‘OK’.
6. To split the contents into separate rows, select ‘Advanced’ and under ‘Split into’, select ‘Rows’. Click ‘OK’ to apply.
7. In this case, the text has been split into columns, which you can rename by double-clicking on the headers.
8. Finally, click ‘Close & load’ to send the new table back to Excel.
Split cell contents to rows using functions
Using Power Query is not the only way to split cell contents into rows; you can combine TRANSPOSE and TEXTSPLIT to achieve the same result.
1. Wrap the TRANSPOSE function around the TEXTSPLIT function, as shown below.
2. Press ‘Enter’ to see the results. However, you won’t be able to drag the formula as usual since the results of the formula need to spill over 4 cells.
Split merged cells
To split merged cells, select them and click the toolbar button with the merge options. Select ‘Unmerge cells’.
The merged cell has been split, and the contents transferred to the cell on the left.
Conclusion
You have a lot of options when it comes to splitting cells in Microsoft Excel. You now know how to split cell contents using the text-to-columns tool, where you can specify the delimiters in the original text and specify the data types in the new split cells. You also know how to use the flash fill tool to split cell contents based on a pattern. If you need to keep the split cells linked so that they are updated with changes, you know how to use built-in functions.
Depending on your version of Microsoft Excel, you can use the TEXTSPLIT function directly or combine other text functions, such as SEARCH, LEN, RIGHT, and LEFT. However, Power Query offers the most flexibility when it comes to splitting cells, as well as keeping the data connected. Finally, you also know how to split cell contents into rows instead of columns and how to split merged cells.
To learn more about working with cells in Microsoft Excel or Google Sheets, take a look at the articles below.
English 
Español 
Português do Brasil 
Français