If you have content in different cells and you want to combine it, there are a couple of useful functions you could use – such as the CONCATENATE Google Sheets function and JOIN.
In this tutorial, I will cover examples of how to concatenate in Google Sheets.
Before we jump to examples, let’s first learn about the concatenate operator and the concatenate function.
CONCATENATE Operator in Google Sheets
Ampersand sign (&) is the concatenate operator that you can use to combine cells in Google Sheets.
For example, suppose you have the first name and the last name as shown below:
You can use the following formula to combine the first and the last name.
=A2&" "&B2
Note that I have used a space character in 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 comma, hyphen, semi-colon, etc.
While ampersand concatenates operate works great, it’s useful only when you have a couple of cells to combine. In case you have a lot of cells that you need to concatenate in Google Sheets, it’s better to go for the functions.
CONCATENATE Google Sheets Syntax
Concatenate function in Google Sheets allows you to quickly combine the values in cells.
Here is the syntax of the Concatenate function:
CONCATENATE(string1, [string2, …])
- string1 – this is the initial string (first string).
- string2 – this is the second string that you want to concatenate to the first one. You can specify
Now let’s see a couple of examples of using the CONCATENATE function in Google Sheets.
Example 1 – Concatenate Google Sheets With Space
Suppose you have the data set of first name and last name and you want to join these with space in between.
Here are the steps to do this:
- Type =CONCATENATE( into the desired cell
- Enter the first string, A2 in our example
- Type the delimiter (separator) inside double quote marks, in our case, we want to use a single space, like so: ” “
- 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 3 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 – Google Sheets Concatenate With Separator Like a Comma
Suppose you have the same dataset of names, but instead of combining it with 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 comma:
=CONCATENATE(B2,", ",A2)
Example 3 – How to Combine First and Last Name in Google Sheets With a Running Number
Concatenate function can also be useful when you want to combine the text with a running number.
For example, in the dataset below, you want to get add a running number in front 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 in Google Sheets with the IF Function
To understand how to use the IF function and get Google Sheets to CONCATENATE 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 that represents 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 arguement
So, 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 5:
Here are the steps to building the above formula:
- The logical_expression to test is whether the value in A1 is greater than 5 expressed as A1>5
- Then “NOPE” is the value_if_true
- 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)
- We then clicked and dragged the formula down the column over the appropriate cells
Alternatives to CONCATENATE Function
How to Use the CONCAT Function In Google Sheets
Google Sheet CONCAT strings works exactly the same as CONCATENATE except you can only use two text strings instead of three or more. Any of the below examples that only use two arguments could also use the CONCAT function. CONCAT in Google Sheets is less powerful, so it may be best to stick with CONCATENATE.
Concatenate an Array of Cells with the JOIN Function
Suppose you have the same data set of names (as shown below):, however, in this
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 use the JOIN function that uses 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 that this is an array formula so use Control + Shift + Enter (hold the Control key and the Shift key and then press Enter). When you hit Control + Shift + Enter, it will automatically append ArrayFormula to the formula.
The formula uses CHAR(10) which would add 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 house number in one cell, the street name on another, the city name on another and so on, and you want to combine it to create address labels.
How to Concatenate More Than 2 Columns In Google Sheets with the & Operator
While the CONCATENATE formula can get a little messy with multiple arguements, you can use the & operator more times than you’d need. Just make sure you have the operator on both sides of the central arguments like in the below example.
Google Sheets Combine Text From Two Cells FAQ
How Do You CONCATENATE in Google Sheets?
You just have to:
- Type =CONCATENATE( into an empty cell
- Enter the cell reference for the first value and a comma
- Enter a delimiter in double-quotes for example, if you wanted a space between you would type ” ” add a comma after.
- Enter the cell reference from the second text string
- 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 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 3 Columns in Google Sheets?
The CONCATENATE function in Google Sheets doesn’t limit to two columns, you can add 3 or more arguments to the function without any issues. So, to use the 3 columns with spaces in between it could be along the lines of:
=CONCATENATE(B1," ",C1," ",D1)
Why Is Concatenate Formula Not Working?
- 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 basically means to join together the text strings in Google Sheets, like this:
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 ” “.
Keep Calm and Keep Concatenating
The above examples should keep you busy learning about these useful tools in Google Sheets, if you need to know more or have any questions, please let us know in the comments.
Now You Have an Understanding of the Concatenate Google Sheets Function, You May Also Like the Following Tutorials:
- 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]
Sumit
Sumit is a Google Sheets and Microsoft Excel Expert. He provides spreadsheet training to corporates and has been awarded the prestigious Excel MVP award by Microsoft for his contributions in sharing his Excel knowledge and helping people.
4 thoughts on “The Ultimate CONCATENATE Google Sheets Guide [Easy!]”
I would like to test this to manage hashtags. I have Hashtags listed in 4 hashtag categories to use in certain combinations for certain social media posts.
Also would not have to write out the ‘#’ every time.
I am going to play around with it, any tips?
How about combining A1-B1;A1-B2;A1-B3;A2-B1;A2-B2;A2-B3, etc?
How can I perform this action across multiple rows at once? I need to combine first and last name with a space in between, as per your first example, but I can’t figure out how to do it on every row. Do you just write out the same formula for each row…?
Garrison,
Just drag down from the cell where the concatenate formula is. For example, using Example 1 provided above, you would drag from C2 down. Google Sheets will apply the formula to all the C cells
Comments are closed.