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.


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.

how to use concatenate 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)

Concatenating Two or More Cells in Google sheets

  • 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.

Read More: How to Concatenate Multiple Cells in Google Sheets (11 Examples)


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)

Joining Cells with Space in Google sheets

  • 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.

Read More: How to Add Space with CONCATENATE in Google Sheets


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)

Merging Cells with Separator in Google sheets

  • 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.

Read More: How to Concatenate With Separator in Google Sheets (3 Ways)


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)

Concatenating Cells in New Line in Google sheets

  • 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"))

Using CONCATENATE Function with Date String in Google sheets

  • 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.

Read More: How to Concatenate Strings in Google Sheets (2 Easy Ways)


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")

Combining Text and Formula in Google sheets

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

Read More: How to Concatenate Text and Formula in Google Sheets (7 Ways)


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.


Related Articles

Jawadul Islam Chowdhury

Jawadul Islam Chowdhury

Hello! This is Jawad. I create Google Spreadsheets-related content for OfficeWheel. I enjoy doing research and solving Google Spreadsheet-related problems. I love to learn new things and teach them to others.

We will be happy to hear your thoughts

Leave a reply

OfficeWheel
Logo