How to Unmerge Cells in Excel?

Excel allows users to perform a wide range of functions such as calculating addition, determining averages, macro activation, graphic tools, pivot tables, macro programming language and more. You can create charts and graphs to aid in the visualization and analysis of data. Let’s take a closer look at how to unmerge cells in Excel.

The Merge cells function in Excel is an excellent tool to present the data in a structured and appealing way. It does this by decluttering and aligning your spreadsheet elements. 

However, the merge cells feature comes with its own set of disadvantages and will prevent you from doing even the simplest of tasks in Excel, such as sorting, selecting and data filtering. Thinking of How to Unmerge Cells in Excel? Nonetheless, there is a way to roll back the changes made by the merge cell feature using a simple technique called “Unmerge” in Excel.

Let us understand everything about unmerge cells in Excel in this post, including the different methods to unmerge, 

Unmerge Cells in Excel Using the Excel Ribbon

The simplest and fastest way to unmerge cells in Excel is by using the Merge & Center option in the Home tab.

Suppose you have the dataset as shown above and you want to unmerge the merged cells in Column B and Column C. Then you should follow the below steps –

• Select the cells/range from which you want to unmerge cells in Excel.
• Click the Home tab.
• In the Alignment group, click on the ‘Merge & Center’ icon.
  Then click on the drop-down menu. It will display a list of items, as shown in the below screenshot. Click on the Unmerge Cells option.

The above steps would instantly unmerge all the merged cells from the selection, as shown in the screenshot below.

It must be noted that after selecting the unmerge option, the content of each merged cell will be placed into the upper cell, and other unmerged cells will be empty.

Unmerge Cells in Excel Using Keyboard Shortcut Keys

Select the cells that you want to merge and press the keys – ALT + H + M + U, and it will unmerge all the unmerged cells.

Unmerge Cells and Copy the Original Value to Each Unmerged Cell

One issue with unmerging cells in Excel is that the value in the merged cell gets allocated to the top cell (after the cells have been unmerged). So, if you have a block of three cells that is merged and has a value in it, and you unmerge this block, the value would go to the top cell, and the remaining two cells would be empty.

In a few cases, you might have to do both things simultaneously:
Unmerging the cells and also filling each unmerged cell with the value from the original cell, as indicated in the screenshot below.

To unmerge cells and fill the cells with original values. Follow the below steps:

• Select the cells that have merged, then go to the HOME tab and click on the
  Merge & Center option under Alignment group.
• Then click the Unmerge Cells option in the drop-down menu.
• This will split all merged cells, but only the upper unmerged cells will be filled with data.
• At this point, you will notice that there are some blank cells (which were earlier part of the merged cells).
• Below is an example where we have unmerged the cells, and you can see that only one the top cell retains the value, and the rest are empty (highlighted in yellow).

• Now select the entire table again, go to the Home tab > Editing group, click Find & Select, and then click Go To Special.
• The Go To Special dialog box will look like the below – 

• Click on the Blanks button as shown above, and click OK.
  
• With all the blank cells selected now, Type the = (equal sign) and press the Up Arrow Key on the keyboard. This will pick up the cell reference of the cell right above the active cell and create a simple formula.
• As we want to fill all unmerged cells that are currently blank, press the CTRL + ENTER key to enter the formula in all the selected cells.

As a result of this step, each blank cell is now filled with the value from the previously merged cell as shown below:

Also Read
How to search in MS Excel in simple steps
How to compare two columns in Excel

How to Split Cells in Excel of entire Worksheet

It might sound like a tedious process, but it’ll just take a few clicks. Here are the steps you must follow – 

• Take a backup of the workbook/worksheet. 
• Select the entire worksheet. You can either click the triangle in the top left corner of the worksheet or press CTRL + A to select all cells.

• Now go to the Home tab and observe the Merge and Center option.
  

 If the option is highlighted, then click on unmerge excel cells.

If it’s not highlighted, that means there are no merged cells in the worksheet.

Locating All the Merged Cells in the Workbook

All the methods covered so far would unmerge all the merged cells in the selection.

What if you have been given a poorly structured spreadsheet and you are trying to convert it into something useful. The problem is the sheet contains fairly large data of merged cells you don’t know about.

But wait. The next problem is locating these cells so we can fix them. To do this, we are going to use Excel’s Find option.

So, how do you find merged cells in your worksheet? 

You can use the ‘Find and Replace’ feature in Microsoft Excel to quickly find out all the merged cells, then select only the ones that you want to unmerge and finally unmerge them.

Below are the steps to do this:

• Select the range that has the cells that you want to unmerge.
• With the cells selected, hold the Control key and press the F key (or Command + F if you’re using Mac). Or, in the Home tab, select Find.
• This will open the ‘Find and Replace’ dialog box.
• In the Find and Replace dialog box, click on the Format button. In case you don’t see the Format button, click on the Options button, and the Format button will show up.

4. In the Find Format dialogue box, click on the ‘Alignment’ tab.

5. Click on the ‘Merge cells’ option and click OK.

And now, click either –

(A) Find Next to get to the next merged cell.

(B) Find All to get a list of all merged cells.

If we click on the Find All button in the Find and Replace dialogue box, it will find all the merged cells and show you the cell references of all of these cells right below the dialogue box. Selecting any of these from the list will make it the active cell in the workbook. 
This allows you to quickly navigate to merged cells.

In the Find and Replace dialog box, you can hold the Control key and manually select the cells that you want to unmerge.

Click on the ‘Merge & Center’ option in the ribbon to unmerge all these cells (or use any of the methods covered above). This method allows you to selectively unmerge cells in excel while keeping some merged cells intact.

Ways to Split the Contents of the Merged cell Across Several cells

Generally, you may need to divide cells in Excel in two cases. 

• When you import data from some external source where all information is in one column while you want it in separate columns or,
• You may want to separate cells in an existing table for better filtering, sorting, or a detailed analysis.

So how do you split a cell in Excel?

By using the following Excel features:

• Using Text to Columns (if your merged cells have a specific delimiter that you can use to split the cells)
• Using Fill Flash (if you want to quickly split simple merges, provided the data in your cell are not complex)
• Using Excel formulas (when you need the output to change continuously along with the changes in the input)

How to Separate Cells in Excel by Delimiter?

Suppose you have a list of participants where the participant’s name, country, and expected arrival date are all in the same column:

What we want is to separate data in one cell into several cells such as First Name, Last Name, Country, Delivery Date, and Status.

To have it done, perform the following steps:

• If you want to put the split cells in the middle of your table, start by inserting a new column(s) to avoid overwriting your existing data.
• In this example, we have inserted 3 new columns as shown in the screenshot below:

• In the Convert Text to Columns wizard, choose how to split cells – by delimiter or width.

• In our case, the cell contents are separated with spaces and commas, so we select Delimited and click Next.
  
   
  
• In the next step, specify the delimiters and, optionally, text qualifier.
• You can choose one or more predefined delimiters as well as type your own one in the Other box. In this example, we will select Space and Comma:

Just two more things are left for you to do – choose the data format and specify where you want to paste split data:

• Data format. By default, the General format is set for all columns, which works well in most cases. In our example, we need the Data format for the arrival dates. To change the data format for a particular column, click on that column under Data preview to select it.
• Destination. To indicate to Excel where you want to output the separated data, click the Collapse Dialog icon next to the Destination box and select the top-left most cell of the destination range, or type a cell reference directly in the box. Please be very careful with this option, and make sure there is enough empty columns right to the destination cell.

Finally, click the Finish button, and you are done! As shown in the below screenshot, Excel has perfectly split the contents of one cell into several cells:

Tips:

• Treat consecutive delimiters as one: You must select this option when your data contains two or more delimiters in a row, e.g., when there are few consecutive spaces between words or the data is separated by a comma and a space, like “Ron, Jennings”.
  
• Specifying the text qualifier: When some text is enclosed in single or double quotes, and you’d like such portions of text to be inseparable, select this option. For example, if you choose a comma (,) as the delimiter and a quotation mark (“) as the text qualifier, then any words enclosed in double-quotes, e.g., “Brisbane, Australia“, will be put into one cell as Brisbane, Australia. If you select {none} as the text qualifier, then “Brisbane will be distributed into one cell (together with an opening quotation mark) and Australia” into another (together with a closing mark).
  
• Data preview: Before you click the Next button, you must always scroll through the Data preview section to make sure Excel has split all cells’ contents right.
  
• It is not possible to import the split data to another spreadsheet or workbook. If you attempt to do this, you will get an invalid destination error.

Also Read
How to apply Round Off Formula in Excel
How to change date format in Excel

How to Separate Cells in Excel with Fill Flash?

You can benefit from the Flash Fill feature (Excel 2013 onwards) that can not only automatically populate cells with data but also split cell contents.

Let’s take a column of data from our first example and see how Excel’s Flash Fill can help us split a cell in half:

Insert a new column (Column 1) next to the column with the original data (Customer) and type the desired part of the text in the first cell (Cell B2 in this example). Type the text in a couple more cells in column B.

Click on Data and Flash Fill.

As soon as Excel senses a pattern, it will populate similar data into other cells automatically. In our case, it’s taken 3 cells for Excel to figure out a pattern:

If you are satisfied with what you see, press the Enter key, and all the names will be copied to a separate column at once.

How to Split Cells in Excel With Formulas?

Even if a cell in Excel contains different types of data, a formula to split a cell in Excel requires finding the position of the delimiter (comma, space, etc.) and extracting a substring before, after, or in-between the delimiters.

Generally, you’d use SEARCH or FIND functions to determine the delimiter’s location and one of the Text functions (LEFT, RIGHT, or MID) to get a substring.

For example, you can use the following formulas to split data in cell A2 separated with a comma and space (please see the screenshot below):

To extract the Customer name in B2:

=LEFT(A2, SEARCH(“,”,A2)-1)

To extract the country in C2:

=RIGHT (A2, LEN(A2)-SEARCH(“,”, A2)-1)

Note: Whenever we use a formula to split cells, we have to be careful about the following.

• Before using this to split the cells, check if the adjacent cell has data
• Be aware of how many columns you’ll need for the split data.
• Whenever you are unaware of how many cells you need, make sure to use a new sheet.
• You may also use the sheet columns you used last to not lose any vital data. 

Conclusion

Before unmerging, always make sure that you have a copy of the sheet because once you unmerge the cells, most of the structure will collapse, and it will be tough to retrieve the old structure once again.

Organizing, formatting, text wrapping, enabling macros and formula-based data calculation are just a few of the many tasks MS Excel is capable of performing. Let us now delve deeper into Macros.

Easysheet is a tech blog that features astounding Excel hacks, tips, and tricks. Our goal is to make Excel accessible to beginners and provide them with answers using EasySheet.

If you require assistance with Microsoft Office products, such as Excel, Word, PowerPoint, Publisher, or anything else, we are the best place to turn.