How to Use the CONCATENATE Function in Google Sheets
When working with large data sets in Google Sheets, we often have two cells with text or values that we wish to display together in a single cell. Rather than creating a new column and typing in the values manually, you can use some of the functions Google has built into their spreadsheet software and get the job done in less than a few minutes.
Some of these functions include JOIN, CONCATENATE, and CONCAT. In this article, we will cover the CONCATENATE function, which is arguably the most useful way of combining data in Google Sheets.
What Is CONCATENATE in Google Sheets?
There are two functions that do similar functions: CONCAT and CONCATENATE. Concatenate means linking things together. In Google Sheets, the CONCATENATE function allows you to easily combine the values from two or more cells into a single, separate cell.
The CONCATENATE formula is much more complex than the CONCAT formula. It allows users who wish to combine data from cells in a different format. CONCAT is an older function that originated in Excel and is much more rigid. It doesn’t allow you to add additional spaces or text.
On the other hand, the CONCATENATE formula allows you more control and has a much larger limit on argument strings. This is definitely one of the functions you should learn to startusing Google Sheets like a pro.
The Syntax for CONCATENATE in Google Sheets
The CONCATENATE function uses one required argument. The amount of extra arguments depends on the needs of the specific use case. Here is the syntax for the CONCATENATE function in Sheets:
Here are the arguments to be used in the formula and what they mean:
The strings contained in the formula refer to data in the sheet. They can be a range of cells or individual cells. You can also have entire columns or rows. A valid function’s rules imply that you should have at least one data point, and each range or point needs to be separated using a comma.
If a function has a range that has many rows and columns, the function will list the results in order from left to right and top to bottom.
What Are Delimiters in Sheets?
Delimiters are characters that can be used to separate text strings. These include commas, quotation marks, brackets, pipes, and slashes. In Google Sheets, you could use the delimiters to split the text.
In context to the CONCATENATE function, these delimiters are used as a visual separator and do not serve a purpose in working the formula. This is because the delimiters exist within the quotation marks, meaning that they aren’t executed. Rather, they are only displayed.
When using the CONCATENATE function, you’ll need to add delimiters inside quotation marks to have some kind of separation between the text in the result cell. A simple example would be adding“ ”to a formula to put a space. You could evenuse special characters in Google Sheetsif you wanted to.
Examples of the CONCATENATE Function
Although the syntax for the formula gives us an idea of how the function works, let’s look at a few examples to see the formula in action to expand your understanding.
1. Using a Space When Joining Two Words
We have a few people’s first and last names in two different columns in this example. What we aim to do here is to write the first and the last name together in another third column. As a delimiter, we use a single space to make the words appear smoothly on the result column.
To do this, follow these steps:
This formula uses three arguments:
Once the formula is executed, you will usually get the option forSuggested autofillwhich will intelligently fill all the columns with the formula, so you won’t have to go through the process manually. If that doesn’t show up, you can click and drag the fill handle over the other cells in column C to apply the formula to them too.
2. Numbered Concatenated Data
Numbering your data can be beneficial, especially if you have data groups in a single spreadsheet. In this case, we will use the ROW function in conjunction with the CONCATENATE function to dynamically add numbers beside the names.
We are reusing the same data we used in the previous example. However, to make things interesting, we will first do the last name, followed by a comma and the first name.
Here are the steps you need to follow to do this:
You could also use theSEQUENCE Google Sheets functioninstead of ROW to achieve a similar result.
Wrapping Up CONCATENATE in Sheets
The CONCATENATE function is an extremely handy function in Sheets. It can help you combine two or more cells and make organizing your sheets less hassle. Just remember it’s not a fully compatible function with Excel, something to keep in mind if you’re using both programs.
Are you looking to download your Google Sheets spreadsheets and open them in Excel? Here’s how to do it.
Who asked for these upgrades?
So much time invested, and for what?
Some subscriptions are worth the recurring cost, but not these ones.
Sometimes the smallest cleaning habit makes the biggest mess.
If an AI can roast you, it can also prep you for emergencies.
