How to Concatenate in Google Sheets (Combine Cell Data)

Want to know how to concatenate in Google Sheets? I can help. CONCATENATE combines data between two cells, protecting data from each. If you want to combine data from two places, that’s where you’ll use this function. Read on for my guide on usage, syntax, and screenshotted examples. I’ll show you how to concatenate (also known as the concat function).

Understanding the CONCATENATE Function in Google Sheets

To combine data in two cells or even in a range of cells, you’ll want to concatenate data. Google Sheets has a function specifically for that. Let’s talk about the syntax of the function, then we can explore examples of how to use it.

The CONCATENATE Google Sheets Syntax

The CONCATENATE Google Sheets function allows you to combine the values in cells quickly. You can find the full syntax at Google, or just read on for an easier explanation. As you know, I like to break down how functions operate in order to use them correctly. Here 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, let’s look at some examples of using the CONCATENATE Google Sheets function.

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 CONCATENATE Google Sheets operator. I’ll walk through this step by step 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 that 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 Google Sheets With Separator Like a Comma

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 formula for Google Sheets to combine cells with commas:

=CONCATENATE(b2,", ",A2)

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

The CONCATENATE Google Sheets function can also be useful 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, for the first name, it will be 01 – John Spike. For the second name, it should be 02 – Brad Connor, and so on.

Here is the formula that can get this done:

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

Example 4 – 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 to test is whether the value in A1 is greater than 5 expressed as A1>5
2. Then “NOPE” is 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

The CONCATENATE Operator in Google Sheets

The easiest way to concatenate in Google Sheets is to use the ampersand sign. It looks like this: &. The ampersand functions as the Concatenate Google Sheets operator, which combines cells without having to type out a longer function. For example, say you have two columns of data. The first contains first names. The second contains last names. In a third column, you might want to concatenate data from the first two columns to get the full name. 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. Here’s what the formula looks like:

=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 (also called delimiter), such as a comma, hyphen, or semi-colon.

While the ampersand concatenate works great, it is only useful when you have a couple of cells to combine. If you have a lot of cells that you need to concatenate in Google Sheets, it’s better to go for the functions option.

Alternatives To the CONCATENATE Google Sheets Function

Note that the CONCAT function differs from the CONCATENATE function. Let’s talk about this and a few other alternatives you may want to consider in your workflow.

How To Use the CONCAT Function In Google Sheets

The Google Sheet CONCAT strings work exactly 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. CONCAT in Google Sheets is less powerful, I recommend sticking with the CONCATENATE function instead.

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 (hold the Control key and the Shift key and then press Enter). 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 useful when you have addresses with different parts in different cells in a row (such as the house number in one cell, the street name in another, the city name in another, and so on), and you want to combine it to create address labels. The concatenated results save so much time.

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

While the CONCATENATE formula can get a little messy with multiple arguments, you can use the & Operator more times. Just make sure you have the operator on both sides of the central arguments, like in the below example.

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 use the fill handle or AUTOFILL to apply it to the entire column.

Where Is the CONCATENATE Function in Google Sheets?

You could type =CONCATENATE into an empty cell or 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:

Just remember, if you want to have something between the values, you need to add a delimiter between the arguments between double-quotes. In the above example, we use a space like this ” “. Note that Google also covers this in their help section. You can also check out my video above for an overview on 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 have a better understanding of 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 need to know more or have any questions, please let us know in the comments.

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