.png)
CONCATENATE Google Sheets: The Ultimate Guide [2023]
Learn how to use CONCATENATE in Google Sheets and discover a few tricks for using this function.
If you need to combine information from different cells in Google Sheets, manually rewriting each data entry in one cell is time consuming and frustrating. Fortunately, the Google Sheets CONCATENATE function allows you to easily combine multiple cells, even separating their contents with commas or semicolons.
What is the Google Sheets CONCATENATE Function?
The CONCATENATE function allows you to effortlessly combine the contents of cells or individual strings into one string in one cell in Google Sheets. The best part is that you can apply it quickly to large amounts of data, preventing you from having to manually merge each entry.
Although CONCAT and CONCATENATE are similar functions, CONCAT is very limited. It does not allow you to add delimiters, and you cannot combine more than two cells or strings. Therefore, CONCATENATE is much more practical and functional, and we will cover all of its many uses in this article.
Syntax
=CONCATENATE(cell_1, “delimiter”, cell_2)
cell_1 = string or cell reference for first part
“delimiter” = the separator between the first and second part; common delimiters include spaces, commas, or semicolons, but this part of the formula is optional (remove quotation marks if you do not need to add a delimiter)
cell_2 = the second string or cell reference
You can add as many cells or strings as you would like, each separated by a comma within the parentheses
How to use Google Sheets CONCATENATE
Step 1: Select the cell where you want to add the CONCATENATE function, then type =CONCATENATE followed by the opening parenthesis, as shown below:
Step 2: Once the CONCATENATE function is detected by Google Sheets, select the cells where the strings to be combined are included, separating them with a comma.
You can also type individual strings directly into the function surrounded by quotation marks.
Step 3: After selecting the cells, press Enter.
The animation below will help make it clearer how simple the process is:
Examples
Example 1: Concatenating Multiple Cells With Cell Ranges
If you want to combine multiple cells in Google Sheets without delimiters, you can type a cell range into the CONCATENATE function instead of individual cells or individual strings.
For example, we want to combine all five of these columns into one cell. We would type the range A2:D4 into the function to combine all four of the cells into one.
Example 2: Using Strings in CONCATENATE
You can type the exact strings inside the CONCATENATE function instead of cell references by enclosing the strings in quotation marks.
Example 3: Adding a Space
You can add a space between the concatenated strings by adding “ ” (a space inside double quotation marks) between the cell references.
Example 4: Including Dates
If you need to include dates in CONCATENATE, you must convert them into string first. The easiest way to convert dates into a string in Google Sheets is to type an apostrophe (‘) before typing your date into a cell.
Example 5: Concatenating multiple rows/columns
You can also concatenate a range of cells with more than one row or column in Google Sheets. The CONCATENATE formula will process the range, concatenating it from left to right and from the top row to the bottom row and putting all of the data in one cell.
Example 6: Numbering each Entry
Another neat thing that we can do is add a number before each entry. For example, imagine that we want to number each name in our Google Sheets dataset.
We would type =CONCATENATE(ROW()-1, “ - ”, A2, “ ”, B2).
We use “ROW()-1” to give us the number of the entry because we have a header row. If your dataset does not contain a header row, you can use “ROW().”
Conclusion
CONCATENATE is a wonderful and versatile function for combining information from multiple cells into one cell in Google Sheets. While the CONCAT formula is similar, it is very limited. We have clearly shown how CONCATENATE can fit many of your needs, and the examples above should help you incorporate this useful tool into your Google Sheets arsenal.
However, if you ever need to undo the effects of this function, check out our article about the SPLIT function in Google Sheets, which allows you to break apart data in one cell into multiple cells.
