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.

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:

dataset for concatenate with separator in google sheets

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

using ampersand to concatenate with separator in google sheets

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

using a different separator with ampersand method

Read More: How to Concatenate Text and Formula in Google Sheets (7 Ways)


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, ...])

concatenate function syntax

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)

using concatenate function to concatenate with separator in google sheets

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)

using a different separator with concatenate function

Read More: How to Use CONCATENATE Function in Google Sheets


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, ...])

join function syntax

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

=JOIN(", ",B3,C3)

using join function to concatenate with string in google sheets

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)

Or

=JOIN(", ",B3:D3)

taking advantage of join function's capabilities

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, ...])

textjoin function syntax

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)

Or

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

using textjoin to concatenate with separator in google sheets

Read More: How to Append Text in Google Sheets (An Easy Guide)


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.


Related Articles

Mehrab Imtiaz

Mehrab Imtiaz

Mehrab Imtiaz is a Technical Writer for officewheel.com specializing in everything Google Sheets. Mehrab has always had a profound passion for writing and with his experience with MS Excel and Google Sheets throughout his career, it seems to be a great match when it comes to dishing out tips and tutorials for all to enjoy.

We will be happy to hear your thoughts

Leave a reply

OfficeWheel
Logo