How to Split Cells in Excel

MS Excel or Microsoft is the popular spreadsheet program that most people use for different tasks. Sometimes, we may get the wrongly formatted data while downloading it from the web or getting it extracted from other software. The typical case is when we have different data in the same cell that must be broken up into different cells. The data may be separated by a comma, semicolon, tab, or any other character. In such situations, we need to split the cells in Excel. For example, we might have full names of people in a single cell, and we may need to split these into first names and the last names in consecutive columns.

In this article, we are discussing three simple methods that help us to split cells in Excel, such as:

By using the Text to Columns Feature
By using the Flash Fill Feature
By using the Excel Text Functions

In all of the above methods, we will use the following data set as an example, where we have a single cell with two different items (First Name, Last Name) separated by a comma:

Let us discuss each method in detail:

Split Cells in Excel Using Text to Columns

One of the simplest and commonly used methods to split cells in Excel includes using the Text to Columns tool. This method allows us to split the whole columns with all cells using the desired rules of separating the data. The tool can easily separate the data of the cells, whether the separating text is a comma, space, tab, etc.

Although this particular method is the best way to split cells in Excel, we need to try other ways to split hundreds or thousands of cells. Depending on the data, one method may produce better results than others. The lower the level of data consistency, the more difficult the process becomes.

The method of splitting a cell using the Text to Columns tool are as follows:

First, we must click on the particular cell(s) (the cell that needs to be split) and then navigate to the 'Data' tab. Here, we must select the 'Text to Columns' option.
After that, we will see the 'Convert Text to Columns Wizard'. In this screen, we must choose the file type describing the data of that corresponding file. We select the option 'Delimited', which mainly allows us to separate the selected data at each specific character occurrence.
In our case, the comma character is the delimiter that separates the cell's items. After selecting the 'delimited' option, we need to click on the 'Next' button to move on to the next step.
On the next screen, we must specify the specific type of delimiter from the Delimiters group. In our case, we select the 'Comma' option and click the 'Next' button. Also, we can see the Data preview, where each item is separated correctly.

Depending on the availability of delimiters, we can also select multiple delimiters to split the data correctly or as desired.
Lastly, we must specify the data format for each column accordingly. Since we have text data in the cell, we must choose the 'General' option or the 'Text' option from the list. It is also the default formatting option. If the preview is displayed correctly after following all the previous steps, we must choose the destination cell. Next, we need to click on the 'Finish' button.

After this, Excel will split the data accordingly.

Since we had one comma in the cell, so the data is separated into two columns. That is how splitting the cells works with the Text to Columns feature in Excel.

Split Cells in Excel Using Flash Fill

The flash fill feature is another handy way of splitting cells in Excel. However, this feature is only available in Excel 2013 and above. The flash fill method works by self-identifying the patterns and then applying corresponding patterns for all the consecutive cells. This method only works when we need to split the data into the connected cells.

Flash fill typically works in two ways:

Background Execution by Excel
Manually Triggered wherever needed

Background Execution by Excel

Excel automatically tries to find certain patterns in the background and then suggests users make the changes accordingly. Let us understand this with an example:

With the original data on the left, we need to type the text element that we want to extract to the next column. Suppose we need to extract only the first names. Thus, we type the first name in the next column in the first row.
Again, we type the first name in the next column in the second row. Now, we see that Excel automatically provides recommendations based on the last actions. We can press Tab or Enter key to accept all the recommendations and flash-fill the data.
This way, we get the first names separated into the next column.

Similarly, we can extract the last names instead of first names.

Manually Triggered wherever needed

If Excel does not provide recommendations to split the data, we can manually trigger Flash fill in Excel. Let us now extract the last names using manual execution:

We need to type the last name into any corresponding row with the original data on the left.
Next, we need to select all the cells in the range that we want to fill. In our case, we select the entire column with the data on the left side (C2 to C8).
After that, we must go to Home > Fill (drop-down) > Flash Fill. Alternately, we can press the shortcut 'Ctrl + E' to perform the same task. This will automatically fill last names into the selected cells.

That is how we can split the cells in Excel using the Flash fill. We can try any of the Flash fill methods, such as the background or manual methods.

Split Cells in Excel Using Text Functions

The last and most efficient method to split cells in Excel includes the use of specific functions. This method is dynamic and works with any data. Although this method allows us to split data with certain rules applied to programs, we must have some advanced Excel skills. While the method is powerful, it is not just a point-and-click method. Instead, we need to efficiently think and apply proper functions with proper rules to split cells accordingly.

The following are the useful functions required for splitting Excel cells in many cases:

LEFT

This function returns the specified number of characters set from the beginning of the selected text string/ cell(s).