The CONCATENATE function in Google Sheets is used to merge multiple data strings into one. This allows you to combine text within multiple cells together. One of the most common examples, when the CONCATENATE function is used, is when you need to merge the two columns containing first names and last names into a single column. Combining texts within different cells can help to organize your spreadsheets more clearly, not only reducing the number of columns within your sheets but also allowing you to identify or manage your data more easily.
In this article, you’ll learn how to use CONCATENATE in Google Sheets, and provide a variety of examples both for how to use the function in different ways to achieve different results. You’ll learn how to use CONCATENATE to combine two entire columns, how to CONCATENATE cells with spaces, commas, and separators, how to combine the CONCATENATE function with the IF formula, and finally, alternatives to the CONCATENATE function.
How to use CONCATENATE in Google Sheets?
First, let’s take a look at the syntax for the CONCATENATE function in Google Sheets:
=CONCATENATE(data_string1, "delimiter", data_string2, …)
- data_string: reference to a cell (e.g. A1)
- delimiter: any text you wish to add between the cell text (surrounded by quotation marks) – you can also leave these empty to create spaces, as you will see further into this article.
In the following example, I want to use the CONCATENATE function to combine the text within the ‘First name’ and ‘Last name’ columns together, in order to have a single column named ‘Full name’. These may also be referred to as CONCATENATE data strings. The following steps will show you how to CONCATENATE two columns in Google Sheets.
1. Open your Google Sheet and click on an empty cell where you want your combined text to be. In this example, I will add a new column next to the cells I want to combine.
2. Enter the CONCATENATE function, making sure you include the cells you wish to combine. In this case, my formula will be =CONCATENATE(A2,B2), combining the data from ‘A2’ and ‘B2’ into a single cell.
3. There are 3 ways to apply the CONCATENATE function to the rest of your rows:
a. Google Sheets may trigger an autofill for the rest of your rows. You can click the tick to apply
b. Select the cell containing your combined text. Click on the bottom right-hand corner of the cell and drag it down over the rest of your rows to apply the function.
c. Fill the whole column with a single formula. CONCATENATE can’t do this on its own: given a range such as A:B, it joins every cell into one long string. Use ARRAYFORMULA with the ampersand instead, for example =ARRAYFORMULA(A2:A20&B2:B20).
As you can see from the screenshot below, the text within the ‘First name’ and ‘Last name’ columns has been combined.
How to CONCATENATE cells with spaces in Google Sheets?
As you may have noticed from the screenshot above, the CONCATENATE function has not added spaces between the text from each cell. This may cause issues with legibility, especially when you share your Google Sheets file with someone else.
However, there is an easy way you can CONCATENATE cells with spaces. Simply add a space between quotation marks (” “) as the delimiter in your formula, in order to create spaces between your text in a combined cell.
1. Click on the cell you wish to add your combined cells to. Enter your CONCATENATE formula, making sure to reference the cells you want to combine, and also the spaces. It should look something like this: =CONCATENATE(A2," ",B2)
2. Apply the CONCATENATE function to the rest of your rows, either using the autofill, drag-down, or data range method highlighted in the section above.
As you can see, the combined cells now include a space between each text.
How to CONCATENATE cells with commas in Google Sheets?
Similar to adding spaces in the CONCATENATE function, to add a comma, simply add a comma and a space between quotation marks (“, “) as the delimiter to your formula. In this example, let’s say I want to format my ‘Full name’ column so that the last name comes first, followed by a comma and the first name (i.e. ‘Last name’, ‘First name’).
1. Click on the cell you wish to add your combined cells to. Enter your CONCATENATE formula, making sure to reference the cells you want to combine, and also the commas. It should look something like this: =CONCATENATE(B2,", ",A2)
2. Apply the CONCATENATE function to the rest of your rows, either using the autofill, drag-down, or data range method highlighted in the how to use CONCATENATE in Google Sheets section.
As you can see, the combined cells now show the last name, a comma and the first name.
How to CONCATENATE cells with separators in Google Sheets?
Sometimes, combining cells together in Google Sheets can get confusing, as there may not be enough context that determines the relationship between the two. For example, let’s say I wanted to combine the cells that refer to the order information – ‘Product order’, ‘Quantity’ and ‘Total’. Combining these on their own may be quite confusing without any additional context.
Using separators, i.e. text between each cell as a delimiter, I can add more information to make the relationship between each product clear. In this example, I want to add separators so that my combined cell contains the following: (‘Quantity’) orders of (‘Product order’), costing (‘Total’).
1. Click on the cell you wish to add your combined cells to. Enter your CONCATENATE formula, making sure to reference the cells you want to combine, and also adding your separators. In this example, my CONCATENATE formula would look like this: =CONCATENATE(E2," orders of ",D2,", costing ",F2).
2. Apply the CONCATENATE function to the rest of your rows, either using the autofill, drag-down, or data range method highlighted in How to use CONCATENATE in Google Sheets section.
As you can see, adding text to create separators between my formula has provided the context I need to better understand my data.
How to combine multiple columns using CONCATENATE?
The CONCATENATE function is also really easy to apply to multiple columns, as you can see in the section above. Simply continue adding your cell references to the formula, separated by a comma. Here is another quick example of how to do it.
Let’s say I want to combine all of the customer data into one cell – ‘First name’, ‘Last name’, and ‘Address’.
1. Click on the cell you wish to add your combined cells to. Enter your CONCATENATE formula, making sure to reference the cells you want to combine, and also adding your delimiters (as spaces, commas, or separators). In this example, my CONCATENATE formula would look like this: =CONCATENATE(A2, " ",B2,", from ",H2).
2. Apply the CONCATENATE function to the rest of your rows, either using the autofill, drag-down, or data range method highlighted in How to use CONCATENATE in Google Sheets section.
Conditional concatenate using IF formula in Google Sheets?
You can also combine the CONCATENATE function with other formulas in order to apply more complex conditions to your data. For example, you can use CONCATENATE with the IF formula to only combine certain cells that meet specific criteria.
For example, let’s say I want to check whether I can approve the order status for each product. If the quantity of each product is over 8, then the order is automatically delayed. I can use the CONCATENATE and IF functions together so that any orders over 8 return an “Automatically delayed” text. If an order is below 8, then the ‘Product order’ and ‘Quantity’ cells will be combined.
1. Click on the cell you wish to add your combined cells to. Create a formula starting with your IF formula, followed by the CONCATENATE formula. In this example, the formula would look like this: =IF(E2>8,"Automatically delayed", CONCATENATE(D2,", ",E2))
2. Apply the CONCATENATE function to the rest of your rows, either using the autofill or drag-down highlighted in the how to use CONCATENATE in Google Sheets section.
As you can see, every order with a quantity above 8 has returned an “Automatically delayed” text. Every order below this value has successfully combined the ‘Product order’ and ‘Quantity’ cells.
Alternatives to the CONCATENATE function: ampersand and JOIN
There are alternatives to the CONCATENATE function in Google Sheets, including CONCAT, which joins exactly two values. These work in almost exactly the same way, so it’s useful to try these to see which one you prefer.
CONCATENATE alternative: Use ampersand (&) in Google Sheets
You can replace your CONCATENATE function by adding an ampersand (&) between each cell reference. You can continue using delimiters to add spaces, commas or texts between your cell content.
1. Click on the cell you wish to add your combined cells to. Make sure to add an ampersand between each cell reference and delimiter: =A2&" "&B2
2. Apply to the rest of your rows, either using the autofill or drag-down highlighted in the how to use CONCATENATE in Google Sheets section.
CONCATENATE alternative: Use the JOIN function in Google Sheets
The JOIN function is a similar formula used to combine cells. The main difference is that the delimiter goes first, at the beginning of the formula, and JOIN places it between every value for you.
The syntax looks like this:
=JOIN(delimiter, value_or_array1, value_or_array2, …)
1. Click on the cell you wish to add your combined cells to. Make sure to reference the correct cells you want to combine and add the delimiter. In this example of joining the ‘First name’ and ‘Last name’ cells together, the formula will look like this: =JOIN(" ",A2,B2)
2. Apply to the rest of your rows, either using the autofill or drag-down highlighted in How to use CONCATENATE in Google Sheets section.
Conclusion
The CONCATENATE function in Google Sheets is a great tool to use when you need to organize or manage your data; not only can it make your spreadsheet easier to understand, but it can also add important context to your groups of data.
Although there are alternatives to the CONCATENATE function, such as using the ampersand (&) or the JOIN function, the CONCATENATE function offers more flexibility and complexity in your actions. Not only can you combine cells containing text, but also CONCATENATE a date, numbers, or other formats.
What’s more, you can combine the CONCATENATE function with other complex formulas like the IF formula, to create complex conditions to organize your data.
Looking for more information on how to better combine, transfer or reference your spreadsheet data? Take a look at our related articles:
English 
Español 
Português do Brasil 
Français