September 15, 2020

Three Ways to Merge Cells in Google Sheets

Google Sheet spreadsheet
SECTIONS
  1. Combining Text - CONCATENATE() Function
  2. Combine Text and Numbers Together using TEXTJOIN() Function
  3. Combine Text and Numbers Together with More Flexibility
  4. Adding Line Breaks
  5. Learn More!

Sometimes, instead of splitting cells, you need to merge cells containing different information. In this tutorial, we will learn three ways using three functions to merge cells into a single cell, with additional formatting, in Google Sheets.

Combining Strings - CONCATENATE() Function

The CONCATENATE() function is a simple but powerful function that can combine both cell content and user-specified string. Let us have a simple example:

Simple concatenate function. Data combined without space in between
Simple concatenate function. Data combined without space in between

The formula used is =CONCATENATE(C2,B2). We can improve it by adding extra characters. Let us try the formula =CONCATENATE(C2,”, “,B2) :

Same table above with commas to improve presentation
Addition of few characters improve the presentation of the data.

Combine Strings and Numbers Together Using TEXTJOIN() Function

The real challenge comes from combining both strings and numbers together. Google Sheets store numbers in different ways and that gives us a wide range of styles depending on what the numbers are for. 

This means that the original number that was entered into the spreadsheet can be wildly different from what is presented to us by Google Sheets.

One quick way to combine string and numbers from different cells together is by using the TEXTJOIN() function. The TEXTJOIN() function requires you to specify a separator and the range of cells to combine. Its syntax looks as follows:

TEXTJOIN(“<delimiter>”, <ignore_empty>, <text1>, <text2>, <text3>)</text3></text2></text1></ignore_empty></delimiter>

The first portion is where you specify the delimiter between the string and numbers to combine, enclosed in double-quotes. 

The <ignore_empty></ignore_empty> is where you specify whether to skip empty cells or not. This is not optional; if you did not leave a portion for <ignore_empty>, the formula will return an error. This is set to FALSE by default. There are cases that you might have to set it to TRUE. To see how it affects the final output, check the image below.</ignore_empty>

The last portion is where you can specify the specific cells or an array of cells to include. 

Let us have an example involving both names and currency. We want to write down the name of the person written in column A, followed by his/her payment listed in column E. The two data are separated with a “ - “ delimiter. We will use the following formula:

=TEXTJOIN(" - ", true, A2, E2)

The result will look like this:

name and payment combined into one using TEXTJOIN() function, with a delimiter used. 
Name and payment combined into one using TEXTJOIN() function, with a delimiter used. 


There is a disadvantage to TEXTJOIN(), however. It uses the same delimiter to separate the entries to be merged. You will learn another technique in the next section. Scroll on!

Combine String and Numbers Together with More Flexibility

While the TEXTJOIN() function allows you to seamlessly combine strings and numbers into one cell, the main disadvantage is that it uses the same delimiter to combine different cells. If you need more flexibility in delimiters, here is another technique.

Let us have an example involving currency. In order to correctly print numbers involving money, we need to use the TEXT() function. The TEXT() function requires the cell where the number is stored and the format that the function will use to convert the number into a string. For our example below, we used TEXT(E2,"$#.00") to convert the number into a string. We used it as part of the formula for combining strings and numbers: 

=A2&" - "&TEXT(E2,"$#.00")
name and payment combined into one without losing formatting
Name and payment combined into one without losing formatting.


To further demonstrate its flexibility, we will merge all three payments with the same name into one cell using the following formula:

=A2&": "&TEXT(C2,"$#.00")&", "&TEXT(D2,"$#.00")&", "&TEXT(E2,"$#.00")

The result will look like this:

Account Name and the payments for three months listed together, with different delimiters used.
Account Name and the payments for three months listed together, with different delimiters used.


As you can see, we have used two delimiters: one to separate the name from the payments, and another to separate the payments from each other. 

We will have another example combining names and dates. For the dates, the TEXT() function will look as follows:

= TEXT(H2,"mmm-dd-yyyy")
Same chart as above with Name and date combined
Name and date combined.

Adding Line Breaks

If you want to add line breaks, you should add char(10) but without using the CONCATENATE() function, as it cannot process line breaks.  Let us apply it to the same example as above, using the formula:

=C2&char(10)&B2
Name formatted with line break in cell
Combined text but in different lines in the same cell.


The same delimiter can be used in TEXTJOIN(). For the same result as above, the following formula can be used:

=TEXTJOIN(char(10),,C2,B2)

Learn More!

Hope you enjoyed this tutorial! To help you learn, here is the sample sheet we used:

Cell Merging Sample Sheet


You can also do so much more using these commands. You can check our other relevant tutorials below:

Suscribe to get more data and analytics tips!

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.