In this article:

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. 

Automate repetitive tasks with Lido

Save hours on repetitive and tedious work. Lido is a new spreadsheet that connects your spreadsheets, forms, PDFs, and email inbox.

Trigger
Action
Select trigger
When a cell value in Google Sheets changes
Check Google Sheets for today's date
When a new row is added to Google Sheets
On new Google Form submission
Send me a daily reminder
On new Typeform submission
When a cell value in database changes
Check database for today's date
When a new row is added in database
When a new HubSpot customer is created
Select action
ADDCALENDAREVENT
Creates a one- or multiple-day calendar event with optional attendees
ADDHUBSPOT
Adds an object to Hubspot
ADDSLACKCHANNEL
Create a Slack channel, and optionally add a topic or members
CALLURL
Makes any HTTP request
CREATEGOOGLEDOC
Replaces [@column_name] values in a Google Doc with the corresponding table row's values
CREATEPDF
Replaces [@column_name] values in a Google Doc with the corresponding table row's values, then export it as a PDF
FETCH
Makes any HTTP request and returns the response
INSERTROWS
Inserts given array below defined values in given worksheet
SENDGMAIL
Sends an email using your Google account
SENDOUTLOOK
Sends an email using your Microsoft Outlook account
SENDSLACK
Sends a Slack message
SENDSMS
Sends an SMS message using your Twilio account
UPDATECELL
Updates cells with given values
UPDATEHUBSPOT
Updates a property of a Hubspot object
Trigger is required
Action is required
Get started