How To Unmerge Cells In Excel

Sometimes you get data from your colleague or from a web server that has merged cell for the sake of formatting or any other reason. Now when you try to do certain tasks on these excel sheets, you face annoying difficulties. Like when try to copy cell, it show area doesn’t match, You can’t select individual columns that if they have any merged cell from adjacent column, can’t sort, can’t filter etc.

So the questions are

How to unmerge cells in Excel
Unmerge cells and fill each cell with the original value
How to find merged cells in Excel
How to unmerge all merged cells in a worksheet
Split the contents of the merged cell across several cells

They are not that difficult tasks but they are essential, ans you should know how to deal with these problems. So, let’s solve them one by one.

How to Unmerge Cells in Excel

So when you have two or cell merged and you want to unmerge them, use merge and center button.

Steps to unmerge cells

Select merged Cells
Go to home tab> Click on “Merge & Center” in Alignment Group.
And Its done. The cells are unmerged.

Same thing can be done using shortcut ALT>H>M>C sequential.

Now remember that the value that was in the merged cell will be only in left-upper cell and other cell will be empty, blank, null, void and black hole as you can see in the below image.

This leads to our next mission of how to Unmerge cells and fill each cell with the original value.

Unmerge cells and fill each cell with the original value

Previously we unmerged cells in column A that has region. After unmerging them we only have that data in upper most cells. We wanted to unmerge and have all unmerged cell the original value. But excel doesn’t seem to do it for you.
To fill these cell with original values follow these steps.

- Select all unmerged cell in excel where you want fill original data.

- Press Ctrl+G. The goto option will appear. Click on Special button.

- - - Same thing can be done by going to Find & Select in home button
  - Then clicking on special.
- Click on “blank” option and then hit OK button.
- Now you have all blank cell selected. Now type = and hit arrow up button. For this example it will select A2. In formula bar you’ll have =A2

Now hit CTRL+Enter. Each cell will be filled with original Value. As you can see in below image.

How to Find Merged Cells in Excel

Many times you get data on sheet that has many merged cell here and there. To do certain tasks, you would want to know where each every merged cells are.
To find each and every merged cell on excel sheet follow these steps.

- Press CTRL+F. This will open the find dialogue box.
- Click on Option

- Click on Format

- Go to Alignment tab.
- Select merged cell.

- Hit Find All to get list of addresses of all merged cells.

Click Find Next to move from one merged cell to other, horizontally.

You can see all the merged cell here.

How to Unmerge All Merged Cells In a Worksheet

To unmerge all merged cell in on an excel sheet follow these steps.

Select all by pressing CTRL+A.
Now press this keyboard shortcut to unmerge, ALT>H>M>C or press click on Merge and Center menu on home tab.

And it’s done. All the cells are unmerged.

Split The Contents Of The Merged Cell Across Several Cells

We talked a lot about unmerging the cell. But I know that there will be need of splitting data into different cells from once cell. And you want to know how can we split the text of merged cell into separate cell. To do so excel provides a tool called text to column.

So if you have data in cell that you want to split into columns using specified character we can use this feature of Excel.

In this example I am splitting full name into first name and last name. Since we know first name and last name is separated by a space character. We will use it as our separator.

Follow these steps to split data into adjacent columns.

- Select the column that you want to split.
- Add or clear columns in which splitted dat will be written.
- Press keyboard shortcut ALT>A>E sequentially. Or go to Data tab and click on Text to column. This will open Text To Column Wizard.

- Click Delimited option and hit next.

- Now here you can select your special Separator. Select space for this example.

Now Click on Finish.

Now we have our first name in original column and last name in the column next to it. Rename full name column to first name and we are good to go.

Now this same thing can be done using a formula.
*Important. Be careful before using this to split cells. If adjacent cell have any data they will be overwritten. Make sure how many columns you need for splitted data. If you don’t know how many cells you require, use a new sheet. Or use last used columns of your sheet so that no important data is lost

Split First Name and Last Name using Formula

Now this one is a bonus.
Just write this formula in cell B2 to extract last from cell A2, if it has full name.

=RIGHT(A2,LEN(A2)-FIND(" ",A2))

Copy it in below cells and the last name will be in B column. To get first name using formula, write this formula.

=LEFT(A2,FIND(" ",A2)-1)

I am not explaining it here because I have explained it in Extract Text From A String In Excel Using Excel’s LEFT And RIGHT Function.

I’ve tried to cover all possible aspects of unmerging cells and splitting data here. If you still have any thought or specific uncompleted task, feel free to ask it in the comments section below.

Related Articles:

How to Merge Two Columns Without Losing Data in Excel

Excel Shortcut Keys for Merge and Center

How to Consolidate/Merge multiple worksheets into one master sheet using VBA

Popular Articles:

50 Excel Shortcuts to Increase Your Productivity

How to use the VLOOKUP Function in Excel

How to use the COUNTIF function in Excel

How to use the SUMIF Function in Excel