How to Use CONCATENATE Function in Google Sheets

Concatenating values is one of the basic operations performed in Google Sheets. There are several functions in Google Sheets to join cells into one. The CONCATENATE function is used to combine two or more strings in one single value. In this article, we will learn how to use the CONCATENATE function in Google Sheets.

Table of Contents

What Is CONCATENATE Function in Google Sheets?

Concatenation refers to connecting or joining. In Google Sheets this function allows you to join two or more strings into one easily and quickly. We can join two or more cells vertically or horizontally using this function. Here is the Syntax of CONCATENATE function.

Syntax

The syntax of the CONCATENATE function is like this below-

CONCATENATE(string1, [string2, …])

Arguments

ARGUMENT	REQUIREMENT	Function
string1	Required	The initial string.
string2	Optional	Additional strings to append in sequence.

Output

The formula CONCATENATE(“John”, “William”) will concatenate the strings and return JohnWilliam.

6 Suitable Examples to Use CONCATENATE Function in Google Sheets

Suppose that you have a dataset of First Name, Last Name, Date of Birth, and Salary of the employees. Now you need to concatenate these cells differently in Google Sheets.

Follow the examples below to see how to use the CONCATENATE function to join cells in this dataset.

1. Concatenating Two or More Cells

You can easily connect two or more cells in Google sheets using the CONCATENATE function. Follow the below steps.

Firstly, select cell D5 and enter the following formula to concatenate cells B5 and C5.

=CONCATENATE(B5,C5)

Then, press Enter, and cell D5 will result in like following image. You can use the fill-handle tool to copy the formula to the subsequent cells.

Lastly, the final output will be like the following screenshot.

2. Joining Cells with Space

Similarly to the earlier example, you can connect cells with a space in-between the values using the CONCATENATE function in Google Sheets. For that-

In the beginning, select cell D5 and type the following formula.

=CONCATENATE(B5," ",C5)

After that, hit Enter. To copy the formula to the following cells use the fill-handle tool.

Finally, the cells will look like the following image.

3. Merging Cells with Separator

To use separators like commas, and full stops in between the values in Google Sheets while using the CONCATENATE function. Follow the below steps.

First of all, select cell D5 and enter the following formula.

=CONCATENATE(C5,", ",B5)

Now, press Enter and the cell will return the concatenated value. Use the fill-handle tool to copy the formula to other cells.

Finally, the output will look like the following image.

4. Concatenating Cells in New Line

If you need to concatenate data string from the new line, you need to use the CHAR function within the CONCATENATE function. The CHAR(10) formula will return the character for the line break. Follow the below steps.

At the beginning select cell D5 and type the formula below.

=CONCATENATE(B5 &CHAR(10),C5)

Then, press Enter and the D5 will result in the following image. You can use the fill-handle tool to copy the formula to the following cells.

Finally, the output will show a similar result to the below image.

Similar Readings

5. Using CONCATENATE Function with Date String

To concatenate date strings with text strings you need to specify the date using the TEXT function in the CONCATENATE function. You can use date formats like dd/mm/yy and so on. Follow the below steps.

First, select cell D5 and enter the following formula.

=CONCATENATE(B5," ",C5, " was born on ",TEXT(D5,"mm/dd/yyyy"))

After that, hit Enter and cell D5 will return the concatenated string with the date. Drag the fill-handle tool to copy the formula to subsequent cells.

Lastly, the final result will look like the following screenshot.

6. Combining Text and Formula

Using CONCATENATE function, you can join the formula to the text string as well. Follow the below steps to do so.

In the beginning, select cell C11 of the Total Salary row and type the following formula with the SUM function.

=CONCATENATE(SUM(C5:C9)," USD only")

Now, press Enter and the final output will look like the following screenshot.

Things to Remember

Make sure that the cell references you are using are correct and that they refer to cells that contain data.
You can use the CONCAT function to join two cells only.

Conclusion

These are the ways to concatenate two or more text or any other kind of strings in Google Sheets. Please comment below with your doubts or suggestions regarding this article. Visit our website, OfficeWheel, for more articles on using functions.