Laku
Sometimes it is necessary to join or concatenate information from different cells, columns, or rows. To perform this, copy-paste is handy until you have a large amount of data. Fortunately, you can concatenate in Google Sheets when handling huge amounts of information. In such cases, concatenating saves time and automates boring tasks. This article serves as a comprehensive guide to how to concatenate 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.
6 Suitable Ways to Concatenate in Google Sheets
There are multiple ways of concatenating in Google Sheets. Here we demonstrate six different ways of concatenating in Google Sheets using the following dataset.
1. Using CONCATENATE Function
The CONCATENATE function is dedicated to joining strings in Google Sheets. Using CONCATENATE function we can concatenate two or more cells, text with date and text with formula, etc.
Example 1: Concatenating Text of Two or More Cells with Space
In the first example, we go through the basic use of the CONCATENATE function by joining two or more cells.
📌 Steps:
- Initially, we have to concatenate the First Name, Middle Name, and Last Name in the Full Name column.
=CONCATENATE(B5," ",C5," ",D5)- For doing that, we select cell E5 and insert the above formula in the formula bar.
- Once we insert the formula and hit enter we got the desired concatenated output in cell E5.
- Then we use the fill-handle tool to get the concatenated output of the subsequent cells.
- Finally, we got the following concatenated output in the Full Name column, where texts of multiple cells are concatenated.
Read More: How to Concatenate Multiple Cells in Google Sheets (11 Examples)
Example 2: Merging Text and Date
In this example, we are going to concatenate text with date using the CONCATENATE function.
📌 Steps:
- In the beginning, we select cell D5 where we want to get the concatenated output of text and date.
=CONCATENATE(B5, " was born on ",TEXT(C5,"mm/dd/yyyy"))- Next, we write down the above formula in the formula bar and press enter.
- Once we press enter we get the concatenated output in cell D5. Afterward, we use the fill handle tool to get the concatenated output of text with date for subsequent rows.
- After performing the fill handle operation we get the concatenated text with date as follows.
Example 3: Joining Text with Formula
In this last example of the CONCATENATE function, we going to concatenate text with a formula.
📌 Steps:
- First of all, we select cell C14 where we want to get the concatenated output of text with a formula that includes the SUM function.
=CONCATENATE("Total salary is ",SUM(C5:C13), " USD Only")- Then we put the above formula in the formula bar.
- As a result, we get the concatenated output where text is concatenated with the result of the SUM formula.
Read More: How to Concatenate Text and Formula in Google Sheets (7 Ways)
2. Applying CONCAT Function
The CONCAT function is a lighter version of the CONCATENATE function. This offers us to perform concatenating operations of two cells. However, we can concatenate more than two cells by the nested use of the CONCAT function.
Example 1: Combining Text of Two or More Cells
This example deals with how to concatenate two or more cells using the CONCAT function as we did in the case of CONCATENATE function.
📌 Steps:
- So as usual, we select cell E5 to get the concatenated output.
=CONCAT(CONCAT(B5,C5),D5)- Then we put the above formula in the formula bar. This will initially concatenate cells B5 and C5 and then cell D5 with the initial output.
- Once we press enter we get the concatenated Full Name in cell E5. But this time we don’t get any space between portions of the name as we get by using the CONCATENATE function.
- At last, we use the fill handle tool to fill the subsequent rows with the formula written in cell E5.
- Finally, we get the following concatenated output.
Example 2: Concatenating Text and Date
This time we are going to concatenate text with date using the CONCAT function.
📌 Steps:
- At the very beginning of this example, we select cell D5 where we want to get the concatenated output of text and date.
=CONCATENATE(B5, " was born on ",TEXT(C5,"mm/dd/yyyy"))- Afterward, we insert the above formula in the formula bar and hit enter.
- As a result, we get the output in cell D5 where the text is concatenated with a date. Then, we use the fill handle tool to get Concatenated Sentence for all the rows.
- Completion of the fill handle operation gives us the following result.
Example 3: Merging Text with Formula
The CONCAT function can be used to concatenate text with formulas as well.
📌 Steps:
- First of all, we select cell C14 to get the concatenated output of text with formula.
=CONCAT("Total salary in USD is ",SUM(C5:C13))- Then we write down the above formula in the formula bar and press enter.
- Consequently, we get the output in the previously selected cell where the result of the SUM formula is concatenated with text.
3. Using JOIN Function
The JOIN function is another useful function we can use to concatenate cells. Using the previous examples here we demonstrate how you can use the JOIN function to concatenate cells.
Example 1: Joining Text of Two or More Cells with Space
In this example, we will join the text of two or more cells with space using the JOIN function.
📌 Steps:
- Initially, we select cell E5 to get the concatenated result of the JOIN function.
=JOIN(" ",JOIN(" ",B5,C5),D5)- Next, we insert the above formula in the formula bar and press enter. This nested JOIN function initially joins cell B5 and cell C5 then joins cell D5 with the result of the first one.
- Afterward, we use the fill handle tool to get the concatenated output in the subsequent rows.
- Once we have done with the fill handle operation we get the concatenated output as follows.
Example 2: Combining Text and Date
The JOIN function can join cells of text and date like the CONCATENATE and the CONCAT function.
📌 Steps:
- At the very beginning, we select cell D5.
=JOIN(" was born on ",B5,C5)- Next, we write down the above formula in the formula bar. This joins cell B5 and cell C5 by placing ” was born on ” in the middle as a delimiter.
- Afterward, we press enter to get the Concatenated Sentence as output in cell D5. And then we perform the fill handle operation as usual.
- Finally, we get the concatenated output as follows.
Similar Readings
- How to Concatenate Strings with Separator in Google Sheets
- How to Concatenate Values for IF Condition in Google Sheets
4. Employing Ampersand Operator (&)
The Ampersand operator (&) is another useful and elegant operator we can use for concatenating cells. Again we use the previous examples to demonstrate how to the Ampersand operator for concatenating cells.
Example 1: Concatenating Text of Two or More Cells with Space
You can use the Ampersand operator to concatenate the text of two or more cells with space. Follow the steps below to do this.
📌 Steps:
- Initially, we select cell E5 to get the concatenated Full Name of persons.
=B5&" "&C5&" "&D5- Then we write down the above formula in the formula bar and press enter.
- Consequently, we get the Full Name in the previously selected cell as a concatenated text.
- Next, we use the fill handle tool as usual.
- The use of the fill handle tool gives us the following result.
Read More: How to Add Space with CONCATENATE in Google Sheets
Example 2: Merging Text and Date
The AMPERSAND operator can also concatenate text and date as we see by other functions.
📌 Steps:
- At first, we select cell D5 to get the concatenated result.
=B5&" was born on "& TEXT(C5,"mm/dd/yyyy")- Then we put the above formula in the formula bar and hit Enter.
- As a result, we get the Concatenated Sentence as indicated. Then we use fill handle to fill the other rows.
- This is the final image of concatenating text with date using the AMPERSAND operator.
5. Utilizing TEXTJOIN Function
The next but not the last method of concatenating cells is the TEXTJOIN function. We can use the TEXTJOIN function to concatenate cells like the following examples.
Example 1: Joining Text of Two or More Cells with Space
Here, we will join the text of two or more cells with space by using the TEXTJOIN function.
📌 Steps:
- First of all, we select cell E5 to get the concatenated Full Name of persons’ as usual.
=TEXTJOIN(" ",TRUE,B5:D5)- Then we put the above formula in the formula bar.
- As a result, we get the concatenated text of Full Name in the selected cell.
- Next, we use the fill handle tool for subsequent rows.
- Finally, we get the desired output as follows.
Read More: How to Append Text in Google Sheets (An Easy Guide)
Example 2: Concatenating Text and Date
Concatenating text and date using the TEXTJOIN function is easy and reliefs us from using the TEXT function.
📌 Steps:
- First of all, we select cell D5 to get the concatenated output of text and date.
=TEXTJOIN(" was born on ",TRUE,B5:C5)- Afterward, we write down the above formula in the formula bar.
- Consequently, we get the Concatenated Sentence as indicated. Then we use the fill handle tool to fill the other rows.
- This is the final image of concatenating text with date using the TEXTJOIN function.
6. Applying QUERY Function
The last method we discuss here for concatenating cells in Google Sheets is the QUERY function. This method can be helpful for concatenating in some special cases.
📌 Steps:
- Initially, we select cell E5 where we want to get the concatenated output.
=TRANSPOSE(QUERY(TRANSPOSE(B5:D13),,9^9))- Then we insert the above formula along with the TRANSPOSE function in the formula bar.
- Just pressing enter gives us the final output of concatenated texts as follows. This method doesn’t require the use of fill handle tool.
Read More: Google Sheets QUERY Function to Concatenate Two Columns
Things to Remember
- The CONCAT function may be long-winded for a simple task. So, try to use the CONCATENATE function in place of CONCAT function whenever possible.
- Try to avoid using the QUERY function for concatenating in Google Sheets if possible.
Conclusion
In conclusion, we have tried to discuss the common as well as some esoteric methods of how to concatenate in Google Sheets. I believe from now on you can concatenate cells in Google Sheets using methods that best suit the situation. Further, If you have any questions regarding this article feel free to comment below and I will try to reach out to you soon. Visit our website OfficeWheel for most useful articles.
Related Articles
- How to Concatenate Two Columns in Google Sheets
- Get Opposite of Concatenate in Google Sheets (2 Ways)
- How to Concatenate Double Quotes in Google Sheets (3 Ways)
- Concatenate Number and String in Google Sheets
- How to Concatenate With Separator in Google Sheets (3 Ways)
- Concatenate Strings in Google Sheets (2 Easy Ways)
- How to Concatenate If Cell Is Not Blank in Google Sheets (7 Ways)
