How to Split Cells in Microsoft Excel?

MS Excel or Microsoft is primarily considered to be one of the most popular "spreadsheet programs" that are widely used by most people to perform various tasks effectively. And sometimes, we may also get the wrongly 'formatted data' while downloading it from the web or extracting it from other software. The typical case is when we have different data in the same cell that must be broken up into different cells. A comma, semicolon, tab, or other character may separate the data. In such situations, we are required to split the cells in Microsoft Excel.

For example, We might have the full names of the selected people in a single cell, and we may need to split these into first and last names into consecutive columns, respectively.

Now in this article, we are discussing the three simple methods that help us to split cells in Microsoft Excel, which are as follows:

By making use of the Text to Columns Feature.
By making use of the Flash Fill Feature.
By making use of the Microsoft Excel Text Functions.

In all of the above methods, we will be making use of 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 now discuss each of the methods as mentioned earlier in detail respectively:

Split Cells in Microsoft Excel by using the "Text to Columns."

It was well known that one of the most straightforward and most widely used methods to split out the cells in Microsoft Excel effectively is using the "Text to Columns tool."

This method usually allows us to split the whole columns with all cells by just using the desired rules of separating the data. And this tool can easily separate the data of the cells, whether the separating text is a comma, space, tab, etc.

Moreover, this respective method is considered the best way to "split cells" in Microsoft Excel; we must try other ways to split hundreds or thousands of cells effectively. Depending on the data, one method may produce better results than others. The lower the level of data consistency, the more complex the process becomes.

The method of splitting a cell by making use of the "Text to Columns tool" is as follows:

Step 1: 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.

Step 2: After performing the above step, we will encounter the 'Convert Text to Columns Wizard.' And in this screen, we must choose the file type describing the data that correspond must. We need to select the option 'Delimited,' which mainly allows us to separate the selected data at each specific characInccurrence.

As in our case, the comma character is thr that separates the cell's items. And after selecting the 'delimited' option, you need to click the 'Next' button to move on to the next

Step 3: On the next screen, we need to spec delimiter type from the "Delimiters group." In our case, we need to 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.

Step 4: Lastly, we need to specify the data format for each column accordingly. Since we have text data in the given cell, we must choose the 'General' option or the 'Text' option from the given 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." Nextly, we need to click on the 'Finish' button.

And after performing all the above steps properly, Microsoft Excel will split the data accordingly.

The data is separated into two columns since we had one comma in the cell. That is how splitting the cells works with the "Text to Columns feature" in Microsoft Excel:

Split Cells in Microsoft Excel by making use of the Flash Fill

The flash fill feature is the other handy way of "splitting cells" in Microsoft Excel. However, this feature is only available in Microsoft Excel 2013 and above. The flash-fill method self-identifies the patterns and then applies corresponding patterns for all consecutive cells. This method only works when splitting the data into the connected cells.

Flash fill typically works in two ways:

Background Execution by Microsoft Excel.
Manually Triggered wherever needed.

Background Execution by Microsoft Excel

It was well known that the respective Microsoft Excel automatically tries to find out particular patterns in the background. Then it will suggest the users make the changes accordingly. Let us now understand this with the help of an example:

1) With the help of the original data on the left, we must also type the text element, which we usually want to extract to the next column. Let us assume that we want to extract only the first names. Thus, we need to type the first name in the next column in the first row respectively.

2) Again, we need to type the first name in the next column in the second row. We see that Microsoft Excel automatically provides recommendations based on the last actions respectively. We can also press Tabor Enter key to accept all the recommendations and flash-fill the data.

3) And this way, we can quickly get the "first names" usually separated into the next column respectively.

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

Manually Triggered wherever needed.

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

Step 1: First of all, we must type the last name into any corresponding row with the original data set on the left.

Step 2: Next, we must select all the cells in the range we want to fill. We must select the entire column with the data on the left side (C2 to C8) respectively.

Step 3: Afterward, we must go to Home > Fill (drop-down) > Flash Fill. Alternatively, we can quickly press out the shortcut 'Ctrl + E' to perform the same task, and by doing this, it will be automatically filling out the last names into the selected cells as well.

That is how we can easily split out the cells in Microsoft Excel by just using the "Flash fill," and we can try any of the "Flash fill methods," like the background or manual methods.

Split Cells in Excel Using Text Functions

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

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

LEFT

It was well known that the respective "LEFT " function in Microsoft Excel is primarily used to return the specified number of the characters set from the beginning of the selected text string or cell(s) effectively.

For example: Suppose that we make use of this function as:

We will get the result as:

WELC

Argument (4) tells the function only to return the initial four characters of the text string "WELCOME USER."

RIGHT

Now the "RIGHT Function" in Microsoft Excel returns the specified number of characters set from the selected text string or the cell(s) endpoint, respectively.

For example: Suppose we make use of this function:

We will get the result as:

USER

Argument (4) tells the function only to return the last four characters of the text string "WELCOME USER."

MID

And the "MID Function" in Microsoft Excel is used to effectively return the specified number of characters from the middle of the selected text string or the cell(s). However, the starting position and length must be defined.

For example: Suppose we use this function:

We will get the result as:

COME

The second argument (initial 4) gives the function the nth character to start from, and the last argument (last 4) defines the length of the output string. Thus, the four characters starting at position four are "COME" in the string "WELCOME USER."

LEN

And the respective "LEN Function" in Microsoft Excel is used only to return the number of characters effectively available in a selected text string or the cell(s) respectively.

For example: Suppose we make use of this function:

We will get the result as:

It is because the string "WELCOME USER" has 12 characters, including space.

SEARCH

This function typically returns the number of characters at which the specified character or text string is first found. The function reads the given string from the left character to the right and is not case-sensitive.

For example: Suppose we make use of this function:

We will get the result as:

The specified character (space) is initially located at the 8^th position of the string "WELCOME USER." The last argument (1) is optional and is mainly used to tell the function where to start searching the specified character. Similarly, the FIND function is also used, but it is case-sensitive, unlike the SEARCH function.

SUBSTITUTE

And this function typically replaces the specified characters with the newly defined characters in the selected text string or cell(s).

For example: Suppose we make use of this function:

We will get the result as:

WELCOME DEAR

And the last specified argument (1) usually defines which instance to replace. In the above example, only one instance exists, the first one. It is because the text characters "USE" are replaced with "DEA." If we needed to replace more, we could change the number of arguments accordingly.

Let us now apply the necessary functions and rules with our example data:

First of all, we are required to split the "first names" and the "last names" from the given cells, and to achieve this efficiently, we just need to make use of the

LEFT, RIGHT, SEARCH, and LEN functions in the following ways:

First Name

Now for the "first names," we are required to make use of the following function:

We will get the result as:

In which,

The respective "SEARCH" function searches for the position of the space character in the string situated in the A2 cell, respectively. Then, it returns the total number of characters.
Since we don't require space characters in first and last names; thus, we can easily subtract 1 from them.
The LEFT function then extracts the remaining characters from the left side.

Last Name

And for the last names, we should make use of the following function:

We will get the result as:

In which,

The "LEN" function primarily returns the length of the selected text string, which is situated in cell A2 respectively.
And the "SEARCH" function returns the total number of the characters at which the space character is first found in the string.
Then, subtracting out the "LEN" and "SEARCH" functions results in the number of characters left just after the first located space character.
The "RIGHT" function then extracts the remaining characters after the space character to the right.

And after applying both the above functions in all the individual cells, we will get the following results: