Cell splitting in Excel is a quick and easy way to separate different values or variables written in a cell. Microsoft has provided two different ways for users to do this.
Two features, Flash Fill and Text to Column, are the most important and common methods for dividing cells in Excel; However, other methods may be available to achieve a similar result. In the following, we will explain these two features and how to use them.
Separate cells with Flash Fill
Flash Fill feature allows users to quickly split a cell into several cells. This feature can automatically detect and extract values in existing homes and eventually place them in new homes. To use the mentioned method, you must use the 2013 version of Excel software or newer versions.
To illustrate how to use the Flash Fill feature, we will use the following spreadsheet as an example where each person’s letters and scores are written together.
First we click on cell C2 and then we will manually type the name in cell B2 (ie Mahesh).
When cell C2 is selected, click the Data tab in the top toolbar of Excel.
From the Data Tools section of the Data tab, click on the Flash Fill option.
Excel will now add all the names in column B to the cells in column C.
To separate the numbers written next to each name, click on a cell in another column, such as D2, and then enter the number corresponding to cell B2, which in this example is 80.
Set cell B2 to the selected state and click Flash Fill again from the Data tab. All numbers will now be transferred to column D.
If you want to create a lot of new cells, you may need to scroll the page frequently. In such cases, using the built-in Split Screen feature of Excel software can be useful and practical.
Divide cells in Excel with Text to Columns
Text to Columns capability is another method for separating cells in Excel that can be used to define the type of values to be separated. To learn how to use the said feature, we will use the spreadsheet of the previous example again.
Click on the cell you want to split to get started. Note that do not select any of the column headings.
When the cells are selected, click the Data tab from the top toolbar of Excel.
Select Text to Columns from the Data tab and from the Data Tools section.
The Text to Columns Wizard window will now open automatically. In this section, select the Delimited option as shown below, and then click the Next button.
On the next page, in the Delimiters section, you can define one or more characters that separate different values within cells. In this example, the values are separated by commas (,) and the space after them; As a result, select both Comma and Space in the Delimiters section.
Under Data Preview you will be able to see a preview of the cell division result in Excel. If the preview output is good, click Next to go to the next page.
The third step of the Text to Column feature is related to defining the destination of the cell separation. Click on the Destination section and delete the values in it. In the same area, click on the bookmark icon in the right corner to specify where to store the isolated data.
On your spreadsheet, tap cell C2 to enter the data. Then click on the icon below as shown below.
Go back to the Text to Columns Wizard window and select Finish from the bottom of the page.
The values in column B are now separated and placed separately in the new cells in columns C and D.
Source: howtogeek
