Google Sheets Concatenate with Separator (Easiest Way in 2024)

Google Sheets Concatenate with Separator

To concatenate strings in Google Sheets with a separator, you can use either the ‘CONCATENATE’ function, the ‘&’ operator, or the ‘TEXTJOIN’ function. We’ll discuss these methods in more detail below.

Using the 'CONCATENATE' Function

Follow the steps below to concatenate with separators in Google Sheets using the ‘CONCATENATE’ function.

1. Open Google Sheet with Data

Open the specific Google Sheet containing your data. In this example, our data includes first names, last names, and email domains.

2. Click on the Cell Where the Full Name Will Appear

Select cell D2 to input the concatenated full name.

3. Type CONCATENATE Formula 

In cell D2, enter =CONCATENATE(A2, " ", B2) to concatenate the first name from A2 with the last name from B2 using a space as the separator.

4. Press Enter to Display the Concatenated Full Name 

Execute the formula by pressing Enter, and observe "John Doe" appear in cell D2.

Using the ‘&’ Operator 

Follow the steps below to easily set up Google Sheets to concatenate with separators using the ‘&’ operator.

1. Access Google Sheet Containing Data

Make sure your Google Sheet with the dataset is open. We’ll use the same dataset as the previous example for better illustration.

2. Select Cell for Email Address Output

Choose cell E2 for the email address result.

3. Input & Operator Formula for Email Address Creation

In cell E2, input =A2 & "." & B2 & "@" & C2 to craft an email address combining the first name, last name, and email domain appropriately.

4. Execute Formula and View Created Email Address

Click 'Enter' to generate "John.Doe@example.com" in cell E2, illustrating effective concatenation for email creation.

Using the 'TEXTJOIN' Function 

1. Open Sheet with Dataset for TEXTJOIN Operation

Ensure the Google Sheet with your names and domains is ready.

2. Identify Cell for TEXTJOIN Concatenated Result

Highlight cell F2 for the outcome of the TEXTJOIN formula.

3. Apply TEXTJOIN Formula to Merge Data with a Space

Fill in =TEXTJOIN(" ", TRUE, A2, B2) in cell F2 to concatenate the first and last names, using a space as the delimiter and ignoring empty cells.

4. Finalize and Display Data Merged with TEXTJOIN

Press Enter to merge the names into "John Doe" shown in cell F2, completing the TEXTJOIN concatenation process.

We hope that you now have a better understanding of how to set up Google Sheets to concatenate using different methods.

‍

If you enjoyed this article, you might also like our article on how to concatenate commas in Google Sheets or our article on how to set up Google finance in Google Sheets.