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
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.
So when you have two or cell merged and you want to unmerge them, use merge and center button.
Steps to unmerge cells
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.
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.
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.
You can see all the merged cell here.
To unmerge all merged cell in on an excel sheet follow these steps.
And it’s done. All the cells are unmerged.
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.
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
Now this one is a bonus.
Just write this formula in cell B2 to extract last from cell A2, if it has full name.
Copy it in below cells and the last name will be in B column. To get first name using formula, write this formula.
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.
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
50 Excel Shortcuts to Increase Your Productivity
How to use the VLOOKUP Function in Excel
How to use the COUNTIF function in Excel
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.