by Svetlana Cheusheva, updated on
How do you split a cell in Excel? By using the Text to Columns feature, Flash Fill, formulas or Split Text tool. This tutorial outlines all the options to help you choose the technique best suited for your particular task.
Generally, you may need to divide cells in Excel in two cases. Most often, when you import data from some external source where all information is in one column while you want it in separate columns. Or, you may want to separate cells in an existing table for better filtering, sorting or a detailed analysis.
The Text to Columns feature comes in really handy when you need to split cell contents into two or more cells. It allows separating text strings by a certain delimiter such as comma, semicolon or space as well as splitting strings of a fixed length. Let's see how each scenario works.
Suppose, you have a list of participants where a participant name, country and expected arrival date are all in the same column:
What we want is to separate data in one cell into several cells such as First Name, Last Name, Country, Arrival Date and Status. To have it done, perform the following steps:
This section explains how to divide a cell in Excel based on the number of characters you specify. To make things easier to understand, please consider the following example.
Supposing, you have Product IDs and Product names in one column and you want to extract the IDs into a separate column:
Since all of the product IDs contain 9 characters, the Fixed width option fits perfectly for the job:
Beginning with Excel 2013, you can make use of the Flash Fill feature that can not only automatically populate cells with data, but also split cell contents.
Let's take a column of data from our first example and see how Excel's Flash Fill can help us split a cell in half:
Whatever diverse information your cells may contain, a formula to split a cell in Excel boils down to finding a position of the delimiter (comma, space, etc.) and extracting a substring before, after or in-between the delimiters. Generally, you'd use SEARCH or FIND functions to determine the delimiter's location and one of the Text functions (LEFT, RIGHT or MID) to get a substring.
For example, you'd use the following formulas to split data in cell A2 separated with a comma and space (please see the screenshot below):
To extract the name in B2:
Here, the SEARCH function determines the position of a comma in A2, and you subtract 1 from the result, because the comma itself is not expected in the output. The LEFT function extracts that number of characters from the start of the string.
To extract the country in C2:
=RIGHT(A2, LEN(A2)-SEARCH(",", A2)-1)
Here, the LEN function calculates the total length of the string, from which you subtract the position of the comma returned by SEARCH. Additionally, you subtract the space character (-1). The difference goes to the 2nd argument RIGHT, so it pulls that many characters from the end of the string.
The result will look as follows:
If your delimiter is a comma with or without space, you can use the following formula to extract a substring after it (where 1000 is the maximum number of characters to pull):
=TRIM(MID(A2, SEARCH(",", A2)+1, 1000))
As you see, there is no universal formula that could handle all kinds of strings. In each particular case, you will have to work out your own solution.
The good news is that the dynamic array functions that appeared in Excel 365 make the use of many old formulas unnecessary. Instead, you can use these functions:
For more formula examples to divide cells in Excel, please check out the following resources:
Now that you are familiar with the inbuilt features, let me show you an alternative way to divide cells in Excel. I mean the Split Text tool included with our Ultimate Suite for Excel. It can perform the following operations:
For example, splitting the participant details in one cell into several cells can be done in 2 quick steps:
Done! Four new columns with the split data are inserted between the original columns, and you only need to give those columns appropriate names:
Tip. To separate a column of names to the first name, last name and middle name, you can use a special Split Names tool.
If you are curious to see the Split Text and Split Names tools in action, we are welcome to use the download link below. I thank you for reading and hope to see you on our blog next week!
Ultimate Suite 14-day fully-functional version (.exe file)
Table of contents