The need to split cells in Excel normally arise when importing data. Though it is not limited to that. In most cases, Excel might import multiple values into a single column. When this happens, you may need to split the data into separate columns.
You might be able to use functions like the LEFT, RIGHT, and the MID functions to split the data into separate cells, only if the text is all the same length. However, if the data does not follow the same pattern in terms of length, then these functions are helpless. But Excel has two enchanting features that can allow you to split data into separate columns without using any of the RIGHT, LEFT or MID functions.
Thus, in this tutorials, I want to cover everything you need to know about splitting cells in Excel without the use of any formula.
How to split cells without formula
Excel is equipped with two non-formula methods of splitting cells into columns: Text to Columns and Flash Fill.
First Method: Text to Columns
This option will make it effortless to carve your text up into separate columns.
Let’s assume this is the situation:
You imported data into Excel which contains four different columns with column headers as Name, Month, Gender and Age. Under each column header has some data. But Excel being so stubborn failed to split the data into different columns accordingly, but rather placed all the four headers with the entire data into a single column (which is column A). Now you’ve been asked to split this cells into separate columns.
Here’s what to do:
Step #1: Insert enough empty columns to the right.
Step #2: Highlight all the cells you wish to split (see screenshot)
Step #3: Choose Data ➝ Data Tools ➝ Text to Columns (see screenshot)
At this point, a Wizard known as Text to Columns Wizard will appear. It is a dialog box that guides the steps to convert a single column of data into multiple columns.
Step #4: Select Delimited and click on Next (see screenshot)
Note: Delimited means the data to be split is separated by symbols such as tab, semicolon, comma, space or other characters.
Step #5: Select your delimiter (space)
If your data is separated by any symbol such as comma, semicolon or space, just choose it. In our example, the delimiter is space. (See screenshot)
In the screenshot, you’ll notice in the Data preview session that the data is split into four columns. When you are satisfied with the Data preview, click next to move to the next step.
Step #6: Specify the Column data format
Here you can select the column and specify its formatting. In our example, I will choose the general formatting for the columns.
After specifying the format click finish.
As seen in the screenshot, Excel spot where there are spaces in the text and use that spaces as a breaking point to break the text into separate columns.
Second Method: The Flash Fill Method
For many types of data, the Text to Columns Wizard is just too good. At times, however, some data cannot be analyzed by that wizard. Perhaps the Text to Columns Wizard is useless if you want to split variable-width data without delimiters. The Flash Fill might save the day. Flush Fill only works smoothly when the data is very consistent. It uses data recognition to extract data. This feature was introduced in Excel 2013 and hence is not available in the previous versions.
The example below shows a worksheet with text in column A. The goal is to identify the numeric values found in column A, Extract those numbers and put them into a separate cell in column B.
Step #1: Type the first and second numbers
Activate cell B1 and type the first number (20). Type the second number (6) in cell B2.
Step #2: Now select Data ➝ Data Tools ➝ Flash Fill (or Ctrl + E)
Now Excel will recognize the pattern and extract the numbers from the remaining cells in a flash. (See screenshot)
Do you have any question relating to the splitting of cells in Excel? Please let me know in the description below.