How to copy-paste merged cells’ values without blank rows in Google Sheets? Transferring data through a computer’s interface is made easier with copy-paste commands. On Windows, you can use Ctrl+C to copy and Ctrl+V to paste. On Mac, simply use the ‘Command’ key instead of Ctrl. In Google Sheets, these commands can be used to complete tasks quickly. However, the Cut command is also equally useful.
One special command in Google Sheets is ‘Paste Special’. This command becomes important when copying and pasting merged cells without blank rows or spaces. If you copy and simply paste, the values will be pasted as they are. However, if you apply the paste special command (Ctrl+Shift+V), the values will be pasted as un-merged but with blank cells. This can cause issues when pasting the copied values to another application, as shown in the screenshots below.
- Extra blank rows in the pasted (paste special) column (column B) in Google Sheets (Screenshot #1).
- Extra spaces in Notepad (Screenshot #2).
To copy-paste merged cells without blank rows and spaces, there are different approaches you can follow.
How to Copy and Paste Merged Cells Without Blank Rows in Sheets
If your purpose is to copy the merged cells’ values in a column to another column without any blank rows in between, you can use the Filter formula. For example, consider the scenario shown in Screenshot #1. In cell B1, you can use the following Filter formula:
=filter(A1:A,A1:A<>"")
This formula will filter out blank rows and extract the values in column A, leaving behind the blank rows caused by the merging of cells.
Another way to remove blank rows from a merged column of data is to use the Query formula:
=query(A1:A,"Select A where A<>''")
Now let’s address scenario #2. How can we paste the values from column A to Notepad without spaces? There are no extra steps needed here. You can simply use the Filter/Query formula and copy the formula output to Notepad.
Merged Cells to Unmerged Cells – Multiple Columns
In the case of merged cells in multiple columns, you can use the Query formula mentioned earlier. For example, if you have merged cells in column A and B, and you want the Query output in column D, you can use the following formula:
=query({A1:A;B1:B},"Select Col1 where Col1<>''")
This formula will return the values in the merged cells in two columns into a single column. If you want the output in two columns, you can use the earlier filter formula in cell D1 and drag it to the right. This way, the values in each column will be pasted to adjacent cells downwards and towards the right.
That’s all about how to copy-paste merged cells without blank rows in Google Sheets. If you have any more questions or need further assistance, feel free to reach out to us at Crawlan.com.
