How to Split Cells in Microsoft Excel

Microsoft Excel is arguably the best and most commonly used spreadsheet program out there for the Windows Operating System. Excel is full to the brim with some of the most useful and handy tools and features the average user would want in their spreadsheet application of choice, and this includes the ability to merge any number of cells into one single cell. Multiple cells that have been merged into one single cell can also, afterwards, be unmerged or “split” into however many cells there were before they were merged. In addition, Excel is also capable of splitting the contents of one single, unmerged cell (the text located inside it) into two or more cells using a specific formula designated by the user. 

Splitting cells in Microsoft Excel is a pretty simple procedure. Here’s how you can split cells in Microsoft Excel:

How to split a single merged cell into multiple cells

On Microsoft Excel 2000:

1. Highlight the already merged cell you want to split into however many cells there were before they were merged.
2. Click on the Format dropdown menu.
3. Click on Alignment.
4. While in the Alignment tab, uncheck the Merge cells checkbox to split the merged cell into however many cells it was made up of.

On newer versions of Microsoft Excel:

1. Highlight the already merged cell you want to split into however many cells there were before they were merged.
2. In the Home tab, click on the arrow located next to the Merge & Center option.
3. Click on Unmerge Cells in the resulting context menu, and the merged cell will be split into as many cells as there were when they were merged into one cell.

How to split the contents of one unmerged cell into two or more cells

Apart from splitting an already merged cell into however many cells it is made up of, Microsoft Excel is also capable of divvying up the contents of a single, unmerged cell into two or more cells. Excel divides the contents of one cell into multiple cells using a formula that the user specifies according to their needs. To split the contents of one unmerged cell into two or more cells, you need to:

1. Highlight and select the cell whose contents you want to split into two or more cells.
2. Navigate to the Data tab.
3. Click on the Text to Columns option in the Data Tools section. This will bring up the Convert Text to Columns Wizard.
4. Select the Delimited option – this will separate the contents of the selected cell according to where a chosen delimiter (a punctuation mark such as a comma, colon or semicolon, for example) is located, and click on Next. The other available option, Fixed width, splits the contents of the selected cell at specified intervals in character length.
5. Under Delimiters, select whatever delimiter you want Excel to use in order to decide how the contents of the selected cell should be split. You can see what the content will look like following the split in the Data preview section.
6. Click on Next.
7. Under Column data format, select the data format you want Excel to use for the new columns that will be created. By default, Excel uses the same data format for the new cells as the one used for the original cell.
8. Click on Finish to have the selected cell split into multiple cells.

Note: Splitting the contents of one unmerged cell into multiple cells overwrites the contents of the next cell to the very right of the selected cell. That being the case, you should make sure that the cell to the right of the cell whose contents you’re splitting is empty.