On many occasions we will have values in a single cell that we want divide in different cells in Microsoft Excel How can we do it? It is not complicated at all and, luckily, we have two methods to carry it out.
How to split cells in Microsoft Excel with Flash Fill
The fastest way to divide the values into several traps is using the “Flash Fill” function. With this we will be able to automatically extract the values of the cells and place them in several separate cells. To be able to use the function, you must have Microsoft Excel 2013 or higher.
In order to see how to use the function, we are going to use the following spreadsheet. Here we are going to divide the names and brands into several cells.
The first thing we will do is click on cell C2 and write the name that appears in cell B2. In the example, it will be “Mahesh”.
Now we are going to click on cell C2 so that it is selected. Then, we go to the Excel toolbar at the top and click on the “Data” tab.
Once we are in the “Data” tab we will have to go to the “Data tool” section and choose “Flash fill”.
Immediately, Excel will take care of dividing the names of all the records in column B and adding them to column C.
If we want to divide the marks obtained, we will have to click on cell D2 and manually type the marks for cell B2. In this case it will be “80”.
We click on cell B2 so that it remains selected. Then, in the Excel toolbar, we will click on the “Data” tab. We will select the option “Quick fill” and in this way, all the marks will be divided and will be available in column D.
How to do it with text in columns
Another way to split cells in Excel is to use the Text to Columns function. In this, we can specify what separates its values in its cells, the function will use that separator to divide the content of the cells.
As an example, we are going to use the same spreadsheet that we used with the flash fill method mentioned above.
The first thing we will do is click on the cells that we want to divide into several. We won’t have to select any column headers.
With the cells selected, in the Excel toolbar we will click on the “Data” tab.
Once we are in the “Data” tab we will have to go to the “Data tools” section and choose the “Text to columns” option.
Microsoft Excel will show us a “Text to Columns Wizard” window. Here we will have to choose the option “Delimited” and then we will click on the “Next” button at the bottom.
In the next screen, we go to the “Delimiters” section and we will have to select the character or characters that will separate the values of the cells. In the example that accompanies this article, we can see that the values are separated by a comma and a space, so we are going to mark the options “Comma” and “Space”.
We continue in the same window and in “Text to Columns Wizard”, in the “Data preview” section, we can see how the data will look when we divide it into several cells. If we agree, we simply click on “Next”.
In this new window, we will have to delete the content that appears in “Destination”. In this field we are going to click on the icon with the arrow pointing up and we will choose where to save the divided data.
We return to the spreadsheet and click on cell C2 to store all the data divided into that cell. Then we will click on the down arrow icon.
Again, we will be in the “Text to Columns Wizard” window, at the bottom we will click on the “Finish” button. Now the single cell values are going to be split into multiple cells.