How to Concatenate With Separator in Google Sheets (3 Ways)

Separators, or more technically known as delimiters, are a crucial part of making concatenated values in spreadsheets more presentable. Today, we will look at a few ways of how we can concatenate with separator in Google Sheets, and by extension, concatenation of values themselves.

Let’s get started.

Table of Contents

3 Ways to Concatenate with Separator in Google Sheets

1. Using the Ampersand Symbol to Concatenate with Separator in Google Sheets

We start with the ampersand symbol (&) which gives us the simplest way to concatenate values in Google Sheets.

For our example, we will use the following dataset:

The idea is to apply ampersand (&) between the values we want to concatenate. The values can be text, numbers, and even cell references. Separators fall under the text type, and we will be using the comma (,) as our separator of choice for this example.

Our formula:

=B3&", "&C3

To be precise, we have used a comma and whitespace as the separator. Since we are inputting the separators as text, it can take any form.

Here’s our result with a hyphen (-) separator:

=B3&" - "&C3

2. Using the CONCATENATE Function

From this point onward we will be looking at some specialized built-in functions for concatenation in Google Sheets. We begin with a fan favorite: the CONCATENATE function.

The CONCATENATE function syntax:

CONCATENATE(string1, [string2, ...])

If we are to apply separators, the syntax looks something like this:

CONCATENATE(string1, “separator”, [string2, ...])

The idea behind this function is the same as we have seen previously with the ampersand. We simply input our choice of separator in-between the values.

Our formula:

=CONCATENATE(B3,", ",C3)

And that’s that! We have successfully concatenated with separators in Google Sheets using the CONCATENATE function. Just as before, we can use any symbols or text as a separator, as long as it is within quotes (“”), in other words, in text form.

=CONCATENATE(B3," - ",C3)

3. Using the JOIN Function to Concatenate with Separators

Next up we have the JOIN function. This is a special function that specifically concatenates multiple values and takes a delimiter as a field to input between them.

The JOIN function syntax:

JOIN(delimiter, value_or_array1, [value_or_array2, ...])

Understanding this, let’s see how it fares when we apply it to our dataset:

=JOIN(", ",B3,C3)

It does its job quite well, doesn’t it?

However, the JOIN function truly shines when there are multiple values to concatenate or “join” with a single delimiter. As we can see in this example:

=JOIN(", ",B3,C3,D3)

=JOIN(", ",B3:D3)

Meaning, unlike our previous two methods, the ampersand and the CONCATENATE function, you cannot put different separators between different values. JOIN lets you choose only one type of separator.

The JOIN function is more efficient in certain situations.

Alternative: The TEXTJOIN Function

The TEXTJOIN function can be considered as an offshoot of the JOIN function. It works similarly but with an added feature.

The TEXTJOIN function syntax:

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

The added feature we are talking about is the ignore_empty field. This takes a Boolean value of either TRUE or FALSE. TRUE if we want to ignore any empty fields in our range selection, FALSE otherwise.

The TEXTJOIN Function in action:

=TEXTJOIN(", ",TRUE,B3:B9)

=TEXTJOIN(", ",FALSE,B3:B10)

Final Words

That concludes all the ways we can concatenate with separator in Google Sheets. We hope that the methods we have discussed come in handy for your spreadsheet tasks.

Feel free to leave any queries or advice you might have for us in the comments section below.