Microsoft excel, developed by Microsoft Corporation, is a spreadsheet for Windows, iOS, and Android that features graphing tools, calculation, and pivot tables. Excel is mainly utilized in organizing data in companies and businesses and also the financial analysis. Just like in other math operations, Excel provides different ways of the division of cells. One uses according to their preference and the type of task ahead that needs to be solved. When using Excel, one might require split cells for reasons like a downloaded file that is not well split.
Split the cell.
Since in Excel, a cell within the same column cannot be split into smaller cells, a new column must be created. The new column should be placed next to the cell intended to be split. When working with imported data in Excel from other sources, you might need to make changes to the existing format.
Note: It's not possible to split one cell into multiple cells but you can split the contents into adjacent cells. This function is only available for Excel 2013, 2016, 2019 and 2021.
For example, in this tutorial, we will split the following cells into adjacent cells. Each Text will be moved to its own cell.
1. Select the cell that you want to split
The cells with contents that are to be split are to be selected first. The next cell on the right side should have space where the contents will be written. This is because once the cell contents are split, they overwrite in the next cell.
2. Text to column conversion
The next step is to get to the Data tab. On the Data Tools group, choose the text to columns option, and the conversion wizard opens.
4. Select Delimited option.
The window that appears first contains a question on whether your selected data is delimited or the width is fixed. If it's not already selected, choose delimited and click next. Delimited data should have some character splitting it, for instance, a tab or comma. For fixed width, there appears some amount of white space in the data set in between every column.
5. Select delimiter.
To clearly define the limits on where the content to be spitted is supposed to be, you should select the delimiters or delimiter when it's one. You can check how your content will appear after the changes in the section of the data preview. Click next
The final step is selecting the data format to be applied in the formed columns. This action is done in the column data format section. By default, the original cell's content and data format is the same as that in the new columns.
On the other hand, other text functions can be used to split a cell. They include the left character, right, mid, find, and len characters. The left character allows for the extraction of characters from the left side of the data, and the right text character extracts characters from the right side. The mid character extracts characters within the middle of the content while the find character is used in finding a substring within another string. The type of data to be used determines the character to be used and the format required to serve the purpose well.
You can also select and split multiple cells at once.