December 4, 2020

How to merge and split cells in Google Sheet

Let’s first learn about the process of merging the cells in Google Sheet with a simple example. We have four cells containing different text values. To merge all four cells together first select all four cells and navigate to ‘Merge Cells’ under ‘Format’ menu of the google sheet.

Here first we have selected the option ‘Merge All’. In this option all four cells will be merged to a single cell. But after clicking any of the options the below pop up will appear.

As you can see here, except the value in top left most cell, rest values won’t be retained after merging the cells. Click OK. See below the four cells have been merged and containing only the value in top left most cell.

Now let’s see what happens by choosing the ‘Merge Horizontally’ Option on our first four cells.

As you can see above the cells have been merged horizontally and the content of the left most cell in each row is only present in the merged cells.

Now let’s see what happens by choosing the ‘Merge vertically’ Option on our first four cells.

As you can see above the cells have been merged vertically and the content of the top most cell in each column is only present in the merged cells.

Now let’s see the process of splitting the merged cells. See below we have a text in a merged cell containing four different cells. Now select the cell and navigate to ‘Merge Cells’ option under ‘Format’ menu. Then select ‘unmerge’.

As you can see below, the merged cell have been splitted into four cells. Note here the content of the merged cell is now placed at the top left most cell.

In this way we can split the horizontally merged cells as follows. In that case the content of the horizontally merged cell will be placed in the left most cell. Below the horizontally merged cells A1 & B1 have been splitted and the same for A2 & B2. The content of the horizontally merged cells are now placed in A1 and A2 respectively.

In this way we can split the vertically merged cells as follows. In that case the content of the vertically merged cell will be placed in the top most cell. Below the vertically merged cells A1 & A2 have been splitted and the same for B1 & B2. The content of the vertically merged cells are now placed in A1 and B1 respectively.

Now if you want to merge the content of different cells into a single cell then you need to use the below CONCATENATE function.

=CONCATENATE(A1," ",B1," ",A2," ",B2)

We have four different content in cells A1, B1, A2 & B2. We have merged these four content separated by blank(“ “) in cell D1.

Now we will also see to split the content of a cell into different cells based on the specified separator. Like we have a test string in cell A1. Now we have used the below formula using SPLIT function in cell A3. Here we have mentioned the separator as space(“ “) .It means whenever as many times it will get a space in the source text string it will split the next part of the string in a different cell.

=SPLIT(A1," ")

Now we will describe another way of splitting the content of a cell. To split the content of cell A1 first select the cell and then click the option ‘Split text to columns’ under ‘Data’ menu of the google sheet.

Now you have to select the separator in the small box that appeared just below the selected source text cell which is A1 here. 

Here we have chosen the option space as separator and the source text string has been splitted into multiple horizontal adjacent cells starting from cell A1 as follows.

Leave a Reply

Your email address will not be published. Required fields are marked *