How to split cells in Excel

You can use separators and the Convert Text to Columns Wizard to split the contents of an Excel cell across several columns. It’s also possible to split Excel cells that contain multiple values.

How to split cells in Excel quick guide

1. Select the cell that contains the data you want to split.
2. Click on Data > Text to Columns.
3. The Convert Text to Columns Wizard will appear. Select Delimited and click on Next.
4. Select your desired separator and confirm.
5. Decide on a new data format.

How to split cells in Excel that contain data

There are various reasons to split an Excel column or to split values in cells into multiple columns. This can be necessary, if a cell contains multiple values that need to be individually recorded by Excel, which is often the case when dealing with imported data sets.

Additionally, you might need to split Excel cells that were previously merged using the merge function.

Splitting data in an Excel cell into several columns

If you have an Excel cell that contains information you would like to split into several cells, you first need to make sure there are enough free columns available. The number of columns that are free has to at least be equal to the number of values that are to be separated from the Excel cell.

Below, we have a cell that contains the values ‘1’, ‘2’ and ‘3’. First, we need to add separators between the three values. You can use tab stops, spaces, commas or semicolons to do this:

Select the cell and open the Data tab. Then click on the Text to Columns button:

When the Convert Text to Columns Wizard opens, select Delimited as the data type if it’s not already selected. Then click Next.

Now, place a tick in the box next to the delimiter you want to use. If you want to use a character other than the predefined options listed in the delimiters box, you need to tick the box next to Other and enter the character you’d like to use in the input field that’s been provided. Then click on Next:

Now, you can configure the data format of the columns. The General option is suitable in most cases. You can confirm the settings for separating the content in the cell by clicking Finish:

Splitting Excel cells that are merged

If you want to split Excel cells that contain more than one value and have been combined into a merged cell, the steps explained above will not work. But you can split the content among different cells by first undoing the merged cell. Here’s how:

1. Select the merged cell using the left mouse button.
2. Open the Home tab.
3. Click on the symbol for the menu item Merge & Center (located in the Alignment section). Then, select the option Unmerge Cells.

Alternatively, you can unmerge cells this way:

1. Click on the merged cell using the right mouse button and select the option Format Cells….
2. Remove the tick from the box next to Merge cells.
3. Click OK.