Laku
Concatenating strings is necessary to join texts of two or more cells. Using copy-paste we can concatenate strings manually. But, this is strenuous and requires a lot of time. Fortunately, concatenating strings with separator in Google Sheets is almost automatic when we use other functions instead of copy-paste. This article talks about four different functions to concatenate strings with separator in Google Sheets.
A Sample of Practice Spreadsheet
You can download the spreadsheets from the link below. The spreadsheets contain a dataset we use here. After downloading you can practice on your own as we demonstrate here.
4 Easy Ways to Concatenate Strings with Separator in Google Sheets
There are multiple ways to concatenate strings with separators in Google Sheets. Here we discuss four easy ways of concatenating strings with separators using the following dataset.
1. Using CONCATENATE Function
The CONCATENATE function is designed to join strings together. We can easily use separators such as space in the middle of strings when using this function.
📌 Steps:
- Here, we have to concatenate the First Name and Last Name in the Full Name column, with space as a separator in the middle. So initially, we select cell D5 to get the concatenated output.
=CONCATENATE(B5," ",C5)- Then we write down the above formula in the formula bar and press enter.
- As a result, we get the concatenated strings with space as separators, like the following. Next, we use the fill handle tool to get the concatenated output for subsequent rows.
- Finally, we get the following result, where First Name and Last Name are concatenated with a space in the middle.
Read More: How to Concatenate With Separator in Google Sheets (3 Ways)
2. Applying JOIN Function
Another way of concatenating strings with separators in Google Sheets is the JOIN function. We can easily join two cells with separators like hyphens by placing the separator as the delimiter of the JOIN function.
📌 Steps:
- First of all, we select cell D5 to get the concatenated output.
=JOIN("- ",B5,C5)- Then we write down the above formula in the formula bar and press enter. Here, the hyphen serves as the delimiter of the JOIN function and a separator of the cells B5 and C5.
- Next, we press enter to get the concatenated result.
- Afterward, we use the fill handle tool to get concatenated output in the subsequent rows.
- Completion of the fill handle operation gives us the following output where the persons’ name and their states are separated by a hyphen.
Read More: How to Concatenate Number and String in Google Sheets
3. Utilizing Ampersand Operator (&)
The next method of concatenating strings with a separator is the utilization of the Ampersand operator (&). The Ampersand operator works in a similar fashion to the CONCATENATE function. In this method, we use commas as a separator.
📌 Steps:
- Here, we have to concatenate the First Name and State with comma as a separator. For that, we select cell D5 to get the concatenated output.
=B5&", "&C5- Then we insert the above formula in the formula bar and press enter.
- Consequently, we get the following output in the selected cell. Afterward, we use the fill handle tool to get the concatenated output for other cells.
- Finally, we get the following result where First Name and State are concatenated with a comma as a separator.
Similar Readings
- How to Concatenate Values for IF Condition in Google Sheets
- How to Concatenate Two Columns in Google Sheets
4. Using TEXTJOIN Function
The last method we discuss here for concatenating strings with separators is the TEXTJOIN function. The TEXTJOIN function works as same as the JOIN function. However, the TEXTJOIN function takes an additional argument of whether we want to include the empty cells or not. Here we have used a semicolon as a separator.
📌 Steps:
- At the very beginning, we select cell D5 to get the concatenated output.
=TEXTJOIN("; ",TRUE,B5,C5)- Next, we write down the above formula in the formula bar and hit enter. Here, a semicolon serves as a separator of the cells B5 and C5.
- Then we use the fill handle tool to get concatenated output in the subsequent rows.
- Once we complete the fill handle operation we get the following result.
Read More: How to Concatenate Text and Formula in Google Sheets (7 Ways)
Things to Remember
- You may use the CONCAT function to concatenate adjacent cells. But the CONCAT function doesn’t offer us to include a separator between strings.
- Always keep in mind that by using CONCATENATE or AMPERSAND operator you can concatenate as many strings as you want.
- You can’t append more than two strings using a single JOIN or TEXTJOIN function.
Conclusion
In conclusion, I believe from now on you become a pro at concatenating strings with separators in Google Sheets. Furthermore, please feel free to leave a comment below if you have any questions about this article, and I will do my best to get in touch with you as soon as possible. For the most useful articles, please visit our site OfficeWheel.
Related Articles
- How to Get Opposite of Concatenate in Google Sheets (2 Ways)
- How to Concatenate Double Quotes in Google Sheets (3 Ways)
- How to Concatenate in Google Sheets (6 Suitable Ways)
- How to Add Space with CONCATENATE in Google Sheets
- How to Concatenate If Cell Is Not Blank in Google Sheets (7 Ways)
- How to Append Text in Google Sheets (An Easy Guide)
