Ultimate 2024 Guide to the CONCATENATE Google Sheets Function

What is the CONCATENATE Google Sheets tool? The CONCATENATE Google Sheets function combines multiple text strings or data from two cells into one, protecting the data from each cell. It is a helpful tool when combining multiple elements. For example, you could use the formula =CONCATENATE(A1, B1) to join the text in cells A1 and B1.

Continue reading my guide to learn more about the CONCATENATE usage and syntax, along with screenshotted examples to master the concat function.

Understanding CONCATENATE in Google Sheets

When you want to concatenate data, you can combine data from two or a range of cells. Luckily, Google Sheets has a function specifically for that.

To help you understand the CONCATENATE Google Sheets function better, I will first walk you through the function’s syntax. Then, we can explore examples of how to use it.

The CONCATENATE Google Sheets Syntax

The CONCATENATE Google Sheets function allows you to combine cell values quickly. You can find the full syntax on Google Support or continue reading this article for a simplified explanation.

As you know, I like to break down how functions operate to use them correctly. Therefore, below is the syntax of the CONCATENATE Google Sheets function:

CONCATENATE(string1, [string2, ...])

• string1: This is the initial string (first string)
• string2: This is the second string you want to CONCATENATE to the first one, which you can specify

Now, look at the examples below using the CONCATENATE function for Google Sheets to combine cells to give you a better understanding of how to use the function in action.

Related Reading: Google Sheets Convert Text to Number

Example 1 – CONCATENATE Google Sheets With Space

Suppose you have the data set of first and last names and want to join these with space in between. Here’s a quick animation that shows how to use the Google Sheets CONCATENATE strings with a separator function. Follow the step-by-step guide below.

Here are the steps to do this:

1. Type =CONCATENATE( into the desired cell
2. Enter the first string, A2, (in our example)
3. Type the delimiter (separator) inside double quote marks; in our case, we want to use a single space, like so: ” “
4. Enter the source for the second string, B2, in our example

Here is what the formula would look like:

=CONCATENATE(A2," ",B2)

Note: The function has three arguments — the first name, a space character in double quotes, and the second name.

How To CONCATENATE Whole Columns

Once you have the formula in the first cell, you can use the fill handle to click and drag through the rest of the column, too.

Example 2 – CONCATENATE With Comma Google Sheets

Suppose you have the same dataset of names, but instead of combining it with a space in between, you want it in the following format: last name, first name.

Here is an example of Google Sheets combining text from two cells using concatenate with separator:

=CONCATENATE(B2,", ",A2)

As you can see from the example, the two elements (first and last name) have been combined and separated by a comma after using the CONCATENATE function.

Example 3 – CONCATENATE Date and Time Google Sheets

The Google Sheets CONCATENATE formula can also combine things other than strings, such as date and time. For example, if you want to combine the hours and minutes in the following dataset to show the complete time, you can use the Google Sheets CONCATENATE multiple cells formula:

=CONCATENATE(A2,":",B2)

You can also add dates and times together by adding spaces, like in the example below:

=CONCATENATE(A2," ",B2,":",C2)

Example 4 –  How To Combine First and Last Name in Google Sheets With a Running Number

The CONCATENATE Google Sheets function can also be helpful when combining the text with a running number.

For example, in the dataset below, you want to add a running number before each name. So, the first name will be 1 – John Spike. The second name should be 2 – Brad Connor, and so on.

Here is the formula for string concatenation Google Sheets:

=CONCATENATE(ROW()-1," - ",A2," ",B2)

Example 5 – How To CONCATENATE Google Sheets with the IF Function

To understand how to use the IF function and get CONCATENATE Google Sheets strings in the results, you must first understand the IF function, so here’s the syntax for it:

IF(logical_expression, value_if_true, value_if_false)

• logical_expression: An expression or reference to a cell containing an expression representing alogical value, i.e., TRUE or FALSE
• value_if_true: The value the function returns if logical_expression is TRUE
• value_if_false: The value the function returns if logical_expression is FALSE, this is an optional argument

To use CONCATENATE with the IF function, you can nest CONCATENATE as one of the value_if arguments.

Let’s look at an example where we only want to concatenate values if the value in the A column is less than five:

Here are the steps to building the above formula:

1. The logical_expression is used to test whether the value in A1 is greater than 5 expressed as A1>5
2. “NOPE” represents the value_if_true
3. The value_if_false is set to a CONCATENATE function to join cells B1, a command and space, and C1 through the expression CONCATENATE(B1,”, “,C1)
4. Click and drag the formula down the column over the appropriate cells

Related Reading: The Easiest Google Sheets Add Text to Formula Guide

The CONCATENATE Operator in Google Sheets

The easiest way to concatenate in Google Sheets is to use the ampersand sign (&). The ampersand functions as the CONCATENATE Google Sheets operator, combining cells without typing out a longer function.

For example, if you have two columns of data. The first contains first names, and the second contains last names. You can concatenate the data from the first two columns in the third column to get the full name.

Let’s take a look at this using the example below. Here’s what the data set would look like before you begin:

To combine column A and column B (along with a space between them), you can type a simple formula with the CONCATENATE Google Sheets operator. This combines the first and the last names using the following formula:

=A2&" "&B2

Note that I used a space character between the reference for the first and the last name, as I want these words to be separated by a space character. You can use any separator (delimiter), such as a comma, hyphen, or semi-colon.

While the ampersand concatenate works great, it is only useful when you combine a couple of cells. If you have many cells that you need to concatenate in Google Sheets, a better alternative is to use the functions option, which allows you to customize your functions according to your needs.

Alternatives To the CONCATENATE Google Sheets Function

While the CONCATENATE Google Sheets function works great, several alternative functions are equally essential to have in your arsenal. Let’s discuss the other alternatives to add to your workflow.

The first alternative is the CONCAT function in Google Sheets, which differs from the CONCATENATE function:

1. CONCATENATE Google Sheets handles separate strings, while the CONCAT values are limited to text and cells
2. CONCATENATE can join multiple strings, while CONCAT can only join two values.

How to Combine Text From Two Cells in Google Sheets Using CONCAT

The CONCAT strings Google Sheets function works the same as CONCATENATE, except you can only use two text strings instead of three or more. The examples below only use two arguments with the CONCAT function.

While it is helpful to learn the CONCAT function, it is worth noting that it is less powerful. Therefore, I recommend using the CONCATENATE function for Google Sheets when you have multiple string calculations.

CONCATENATE an Array of Cells with the JOIN Function

Suppose you have the same data set of names (as shown below). However, in this case, you want to combine the names and list these in a single cell (each name in a new line). You’ll need to type the JOIN function using the following syntax:

JOIN(delimiter, value_or_array1, [value_or_array2, ...])

• delimiter: The character or string to put in between each value
• value_or_array1: The first value
• value_or_array2: Additional value(s) or array to be appended using delimiter

The following formula can get this done:

=JOIN(char(10),A2:A6&" "&B2:B6)

Note: This is an Array formula, so use Ctrl + Shift + Enter keyboard shortcut. When you hit Ctrl+ Shift + Enter, it automatically adds the Array Formula to the formula. The formula uses CHAR(10), which adds a line break to the result of the formula.

This technique can also be helpful when you have addresses with different information in a row of cells, such as the house number, the street name, the city name, and so on. In such cases, you’ll want to combine this data to create full address labels with the concatenated results, saving you considerable time and effort.

How To CONCATENATE More Than Two Columns In Google Sheets with the & Operator

While the CONCATENATE formula can get messy with multiple arguments, you can use the ampersand (&) operator. Just ensure you have the operator on both sides of the central arguments, like in the example below.

Related Reading: How to Merge Cells in Google Sheets

Frequently Asked Questions

How Do You CONCATENATE in Google Sheets?

You have to:

1. Type =CONCATENATE( into an empty cell
2. Enter the cell reference for the first value and a comma
3. Enter a delimiter in double-quotes. For example, if you wanted a space between, you would type ” ” and add a comma after
4. Enter the cell reference from the second text string
5. Press “Enter“

A full CONCATENATE function could look something like this:

=CONCATENATE(A2," "B2)

How Do I CONCATENATE Two Columns In Google Sheets?

Use the above steps to CONCATENATE one row in the columns, then apply the fill handle or AUTOFILL to the entire column.

Where Is the CONCATENATE Function in Google Sheets?

There are two ways to find the CONCATENATE function:

1. Type =CONCATENATE into an empty cell, and the formula should appear
2. Navigate to “Insert” > “Function” > “All” > “CONCATENATE” to find string concatenation in Google Sheets

How Do You CONCATENATE Three Columns in Google Sheets?

The CONCATENATE function in Google Sheets isn’t limited to two columns. You can add three or more arguments to the function without any issues.

To use three columns with spaces in between, use the following syntax:

=CONCATENATE(B1," ",C1," ",D1)

Why Is the CONCATENATE Formula Not Working?

There are different reasons why the CONCATENATE formula does not work, such as:

• Make sure your cell references are correct
• Make sure you have entered your delimiter between double quotes, so ” ” for a space

What Is a CONCATENATION Example?

CONCATENATE means to join together the text strings in Google Sheets, like the example below:

Add a delimiter between the arguments and double quotes if you want something between the values. In the above example, we use a space like this ” “.

Note that Google also covers this in their help section. Alternatively, you can check out my video above for an overview of the operator and function.

How Do You Undo a Concatenation?

The opposite of CONCATENATE is the SPLIT function. You can use it to split data from one cell into multiple cells. Google covers it here.

Wrapping Up

Now that you better understand how to use the CONCATENATE Google Sheets function, you can start practicing using the examples above as references. They should keep you busy and give you plenty of practical experience in perfecting the CONCATENATE function.

If you want additional practice, check out this online Google Sheets masterclass! It covers advanced functions like QUERY, IMPORTHTML, IMPORTXML, and insider secrets to master tricky Google Sheets features. By the end of the course, you’ll be a confident spreadsheet user who can easily tackle complex data analysis.

Let us know in the comments if you need more or have any questions.

Related:

• How To Quickly Merge Cells in Google Sheets
• The Ultimate Guide to Google Sheets VLOOKUP Function
• How To Use COUNTIF Function in Google Sheets
• Using IF Function in Google Sheets (with Examples)
• The Easiest Google Sheets Add Text to Formula Guide [Step-by-Step]
• How To TRANSPOSE Data in Google Sheets