How to Concatenate String/Text with Separator in Google Sheets

In Google Sheets, you can write a formula to concatenate (combine) multiple text or strings into a single cell. In this tutorial, we will learn three ways to write a formula.

So, without any ado, let’s learn this:

Using TEXTJOIN to Concatenate Strings in Google Sheet

The best and easiest way to do this is to use the TEXTJOIN function. This function is specifically designed for this.

TEXTJOIN(delimiter, ignore_empty, text1, [text2, …])

• delimiter – The string or a seperator that will appear between each text value.
• ignore_empty – A TRUE or FALSE value where TRUE will ignore empty cells in the range.
• text1, text2, … – The text or string values to concatenate.

Now to use this function, you can use the below steps:

1. Enter the TEXTJOIN function in a cell.
2. In the first argument, which is the delimiter, specify the separator.
3. Use TRUE in the second argument to tell the function to ignore the blank cells.
4. Specify the range of cells in the third argument where you have the values you want to combine.

=TEXTJOIN(" ",TRUE,A1:A3)

• ” ” is the delimiter, the character inserted between each text item being joined. In your example, it’s a space.
• The ignore_empty parameter is TRUE. If it’s set to TRUE, the function will ignore any empty cells in your joining range. If it were FALSE, the function would include empty cells.
• A1:A3 is the range from which you need to combine in the single cell.

In this example, we have values in the range A1:A3, which we have used. But if you have strings in non-continued cells, you can define the cell, as in the following example.

=TEXTJOIN(" ",TRUE,A1,A3,A5,)

TEXTJOIN is the best way to combine strings. It allows you to select an entire range in a single go.

There are some other ways in which you can write a formula.

Using AMPERSAND (&)

If you have fewer values to combine, you can use the ampersand. In this way, you refer to the cell with the first value, and then you need to enter the separator. Then, you need to specify the cell with the second value.

=A1&" "&A2&" "&A3

In this formula, A1, A2, and A3 refer to the cells from which you want to get the text. ” ” is a space that you want to insert between the text values from these cells.

That’s how, =A1&” “&A2&” “&A3 will join the strings (text) of cells A1, A2, and A3 with a space as a separator.

Using CONCATENATE function

CONCATENATE works like the above formula, but you don’t need to use ampersand in this function.

Specify the first cell, the separator, the second cell, and the separator again.

=CONCATENATE(A1," ",A2," ",A3)

In this formula, “A1”, “A2”, and “A3” are the cell references. These references point to the specific cells whose contents you want to join together.

The ” ” between the cell references is a space character that acts as a value separator.

This isn’t limited to just three cells – you can concatenate as many cells as you require, like =CONCATENATE(A1,” “,A2,” “,A3, ” “, A4, ” “, A5).

You can also add different kinds of separators, not just spaces.

Using the ARRAYFORMULA + TEXTJOIN

If you have a range of cells and you want to concatenate values from that range with a delimiter, you can use ARRAYFORMULA combined with the TEXTJOIN.

=TEXTJOIN(",", TRUE, ARRAYFORMULA(A1:A10))

This formula, doesn’t make much difference when yo try to compare it with the alon TEXTJOIN method.

Use JOIN Function to Combine Text Values with a Separator

The JOIN function is similar to TEXTJOIN but simple and less powerful, as it automatically considers all cells in a range.

=JOIN(" ", A1:A3)

Note – It’s important to note that if any of the cells A1, A2, or A3 are empty, the formula will still insert a space for that cell.

Key Differences – TEXTJOIN VS JOIN

As I said earlier, TEXTJOIN is more powerful and flexible to use for concatenation comparing with all the method we have shared in this tutorial.