How to Use the CONCATENATE Function in Google Sheets

When working with large datasets in Google Sheets, there are often two cells with text or values that we want to display together in one cell. Instead of creating a new column and manually entering the values, you can use some built-in functions provided by Google in the spreadsheet software to accomplish this task in just a few minutes.

Some of these functions include JOIN, CONCATENATE, and CONCAT. In this article, Quantrimang.com will focus on the CONCATENATE function, which is considered the most useful way to combine data in Google Sheets.

What is CONCATENATE in Google Sheets?

There are two functions that perform similar tasks: CONCAT and CONCATENATE. Concatenate means to join everything together. In Google Sheets, the CONCATENATE function allows you to easily combine values from two or more cells into a separate cell.

The CONCATENATE formula is much more complex than the CONCAT formula. It allows users to concatenate data from cells in a different format. CONCAT is an older function originating from Excel and is much more rigid. It does not allow you to add spaces or text.

On the other hand, the CONCATENATE formula gives you more control and has a much larger limit for argument strings. This is definitely one of the functions you should learn to start using Google Sheets professionally.

The CONCATENATE function in Google Sheets uses one required argument.

The number of arguments depends on the specific use case. Here is the syntax for the CONCATENATE function in Google Sheets:

=CONCATENATE(first string, [second string, …])

Here are the arguments used in the formula and their meanings:

First string: This is the first string to be concatenated.
[Second string]: This is an optional string that is appended to the first string in the concatenation.
…: It means you can add multiple optional strings beyond the second string, for example, a third column or a delimiter.

The strings in the formula refer to the data in the spreadsheet. They can be a range of cells or individual cells. You can also select entire columns or rows. The valid function rules imply that you must have at least one data point, and each range or point needs to be separated by a comma.

If the function has a range that spans multiple rows and columns, the function will list the results in order from left to right and top to bottom.

What are delimiters in Google Sheets?

Delimiters are characters that can be used to separate text strings. They include commas, double quotation marks, square brackets, curly brackets, and forward slashes. In Google Sheets, you can use delimiters to separate text.

In the context of the CONCATENATE function, these delimiters are used as visual separators and do not serve the operational purpose of the formula. This is because the delimiters exist within the double quotation marks, meaning they are not executed. Rather, they are only displayed.

When using the CONCATENATE function, you will need to include delimiters within the double quotation marks to have some form of separation between the text in the resulting cell. A simple example is adding a space ” ” in the formula to insert a whitespace. You can even use special characters in Google Sheets if desired.

Examples of the CONCATENATE function

While the syntax of the function gives us an idea of how it works, let’s consider a few examples to see the formula in action and expand your understanding.

  1. Using a space to concatenate two words

Suppose we have the last name and first name of several individuals in two different columns in this example. The goal here is to write the last name and first name together in a third column. As for the delimiter, the example will use a space to make the words appear smoothly in the resulting column.

To do this, follow these steps:

  1. Click on the empty cell, C3, in the given example.
  2. Enter the first part of the formula, which is =CONCATENATE(.
  3. Now, enter the first argument. In this case, it is the first column containing names, which is cell A2.
  4. Add a comma after the first argument.
  5. Now, add the delimiter. In this case, we are using a space, so type ” ” including the double quotation marks.
  6. Add another comma.
  7. Enter the source for it. In this case, it is cell B2.
  8. Finish the formula by adding a closing parenthesis.
  9. Press Enter to execute the formula.

This formula uses 3 arguments:

The first argument is the cell reference with the name.
The second argument is the delimiter.
The third argument is the last name.

When the formula is executed, you will typically receive a Suggested autofill option that will populate the formula to all the columns, so you don’t have to go through the process manually. If the formula doesn’t appear, you can click and drag the fill handle on other cells in column C to apply the formula to them.

  1. Numbered Linked Data

Numbering the order of data can be beneficial, especially if you have data groups in a spreadsheet. In this case, the example will use the ROW function combined with the CONCATENATE function to add numbers alongside names.

The example is reusing the same data used in the previous example. However, to make things more interesting, we will first perform it with the last name, followed by a comma and the first name.

Here are the steps you need to follow to accomplish this:

  1. Click on the cell where you want to place the formula.
  2. Enter the first part of the formula, which is =CONCATENATE(.
  3. Instead of referencing a cell as an argument, the first argument is the ROW function. Enter the argument as ROW()-1.
  4. The second argument is to add a delimiter. In this case, it is “-” including the double quotation marks.
  5. Enter the first argument, which in this case is the last name in cell B2.
  6. Here, the example adds another separator in the form of “, “.
  7. Add the final argument, which is cell A2.
  8. Close the formula by adding a closing parenthesis.
  9. Press Enter to execute the formula.

You can also use the SEQUENCE function in Google Sheets instead of ROW to achieve similar results.

The CONCATENATE function is extremely useful in Sheets. It can help you concatenate two or more cells and make organizing your spreadsheets less complex. Just remember that it is not entirely compatible with Excel, which is something to keep in mind if you are using both programs.

Leave a Comment

Your email address will not be published. Required fields are marked *

Shopping Basket
Scroll to Top