Seemanto Saha
We often need to concatenate multiple cells while working in Google Sheets. There are several functions that can help us in this regard. However, some of these functions have a few limitations. Hence, we will not be able to each of these functions in every instance while concatenating multiple cells. In this article, I’ll discuss 11 ideal examples that will help you learn how to concatenate multiple cells in different instances in Google Sheets.
A Sample of Practice Spreadsheet
You can copy our practice spreadsheets by clicking on the following link. The spreadsheet contains an overview of the datasheet and an outline of the discussed examples to concatenate multiple cells in Google Sheets.
11 Ideal Examples to Concatenate Multiple Cells in Google Sheets
First, let’s get familiar with our dataset. The dataset contains a list of first names, last names, and jersey numbers of a few football players. We want to concatenate these three data for each player. Now let’s get started.
1. Applying Ampersand Operator
Perhaps, the easiest way to concatenate multiple cells is to connect the cells using an ampersand (&) operator. We can also add different delimiters or spaces in between the strings from each cell using an ampersand operator. Some limitations of this method are its inapplicability to a range that does not contain an equal number of cells for each row/column and a lengthy formula if the row or column contains too many cells. Now let’s dive into the steps of applying this method.
Steps:
- Firstly, select Cell E5.
- Then, type in the following formula-
=B5&" "&C5&" "&D5- Finally, press Enter key to get the required result.
- Now, select Cell E5 again, and then, hover your mouse pointer above the bottom-right corner of the selected cell.
- This will make the Fill Handle icon visible. Use it to copy the formula to other cells of Column E.
Read More: How to Add Space with CONCATENATE in Google Sheets
2. Using Ampersand Operator with ARRAYFORMULA Function
We can use the ARRAYFORMULA function with our previous method to concatenate multiple cells in Google Sheets. If we use the ARRAYFORMULA function, we can perform concatenation operations for a range. Hence, we won’t have to use the Fill Handle icon.
Steps:
- Select Cell E5 and then type in the following formula-
=SUM(C5:C11)- After that, press Enter key to get concatenated strings for a range.
3. Applying CONCAT Function
The CONCAT function is one of the most used functions for concatenation operations. However, this function can take only two arguments as input. Hence, we have removed a column from our dataset. Another limitation of using the CONCAT function is that you can’t include any delimiter or spaces between the strings from two cells.
Steps:
- Firstly, select Cell D5.
- Afterward, type in the following formula-
=CONCAT(B5,C5)- Next, get the required result by pressing the Enter key.
- Finally, use the Fill Handle icon to copy the formula to other cells of Column D.
Read More: How to Concatenate Number and String in Google Sheets
4. Joining ARRAYFORMULA and CONCAT Functions
We can also join the ARRAYFORMULA function with the CONCAT function to perform concatenation operations for a whole column.
Steps:
- Select Cell D5 and then type in the following formula-
=ARRAYFORMULA(CONCAT(B5:B10,C5:C10))- Finally, press Enter key to get the concatenated strings for the range.
Formula Breakdown
- CONCAT(B5:B10,C5:C10)
The CONCAT function performs a concatenation operation for the two values in provided range.
- ARRAYFORMULA(CONCAT(B5:B10,C5:C10))
Here, the ARRAYFORMULA function helps the non-array function CONCAT to deal with an array and display the result as an array.
Similar Readings
- How to Concatenate With Separator in Google Sheets (3 Ways)
- How to Concatenate Strings in Google Sheets (2 Easy Ways)
5. Applying CONCATENATE Function
If you require concatenating more than two cells, then the CONCATENATE function can be the remedy to the limitation of the CONCAT function. The CONCATENATE function can append multiple strings to one another. However, this function also has the same limitations of using ampersand operators to concatenate multiple cells.
Steps:
- First, select Cell E5 and then type in the following formula-
=CONCATENATE(B5," ",C5," ",D5)- Following this, get the required result by pressing the Enter key.
- Now, finally, use the Fill Handle icon to copy the formula in other cells.
Read More: How to Use CONCATENATE Function in Google Sheets
6. Uniting CONCATENATE with CHAR Functions
Instead of manually inserting the delimiters or other symbols between the strings to be catenated, we can unite the CHAR function with CONCATENATE function. The CHAR function can convert a number (decimal, octal, or Unicode) into a character in accordance with the current Unicode table that you can find in Wikipedia. Here, we’ll insert line breaks between the strings from each cell. The control code in decimal for line breaks is 10.
Steps:
- Firstly, select Cell E5.
- Afterward, type in the following formula-
=CONCATENATE(B5,CHAR(10),C5,CHAR(10),D5)- Following this, press Enter key to get the required result.
Formula Breakdown
- CHAR(10)
The CHAR function is used twice and in both instances, it returns a line break (or a line feed).
- CONCATENATE(B5,CHAR(10),C5,CHAR(10),D5)
The CONCATENATE function appends the strings in Cell B5, C5, and D5 with a line break in between.
- Finally, use the Fill Handle icon to copy the formula to subsequent cells.
7. Uniting CONCATENATE and TEXT Functions
If you require to concatenate dates and/or times with strings, you can join the TEXT function with CONCATENATE function. The TEXT function can convert a number into a text value in accordance with a specified format. Here, we’ll perform a concatenation operation for the following dataset.
Steps:
- First, select Cell E5 and then, type in the following formula-
=CONCATENATE(TEXT(B5,"HH:MM")," ",TEXT(C5,"D MMM YYY")," ",D5)- Afterward, get the required result by pressing the Enter key.
Formula Breakdown
- TEXT(B5,”HH:MM”)
This TEXT function converts the time implied by Cell C5 into a text value according to the specified format.
- TEXT(C5,”D MMM YYY”)
Concurrently, this TEXT function transforms the date referred to in Cell D5 into a text value in accordance with the specified format.
- CONCATENATE(TEXT(B5,”HH:MM”),” “,TEXT(C5,”D MMM YYY”),” “,D5)
Finally, the CONCATENATE function appends the strings to return a concatenated string.
- Finally, use the Fill Handle icon to copy the formula to other cells.
Read More: How to Append Text in Google Sheets (An Easy Guide)
8. Merging IF and CONCATENATE Functions
We can also perform conditional concatenation operations by merging the IF function with CONCATENATE function. To demonstrate this method, we have added a new column to our dataset where we have listed the continents they play club football. If any player plays outside Europe, we’ll ignore concatenating the row for that player.
Steps:
- Firstly, select Cell F5.
- Then, type in the following formula-
=IF(E5<>"Europe","Ignore",CONCATENATE(B5," ",C5," ",D5))- Afterward, press Enter key to get the required output.
Formula Breakdown
- IF(E5<>”Europe”,”Ignore”,CONCATENATE(B5,” “,C5,” “,D5))
First, the IF function checks whether Cell E5 contains any other string than “Europe”. If the logical test finds any other string, then the IF function returns the string “Ignore” as output. Else, it returns the concatenated string appended by the CONCATENATE function.
- Now, finally, use the Fill Handle icon to copy the formula to other cells of Column F.
9. Employing JOIN Function
Inserting required delimiters or spaces between the strings from each cell while using the CONCATENATE function can be tedious for large datasets. Implementing the JOIN function can help us in such scenarios. The JOIN function can concatenate multiple cells while using a specified delimiter between them.
Steps:
- Select Cell E5 first.
- Afterward, type in the following formula-
=JOIN(" ",B5,C5,D5)- Next, press the Enter key to get the required concatenated string.
- Consequently, use the Fill Handle icon to copy the formula to other cells.
10. Employing TEXTJOIN Function
The CONCATENATE or JOIN functions can’t execute properly if blank cells are present in the dataset. In such scenarios, we can use the TEXTJOIN function to ignore blank cells. We have added a new row with a black cell to our dataset to demonstrate this method.
Steps:
- Firstly, select Cell C5.
- Then, type in the following formula-
=TEXTJOIN(" ",True,B5,C5,D5)- Afterward, press Enter key to get the required concatenated output.
- Finally, use the Fill Handle icon to copy the formula to other cells of Column E.
Read More: How to Concatenate If Cell Is Not Blank in Google Sheets (7 Ways)
11. Combining TRANSPOSE and QUERY Functions
Another way to concatenate multiple cells for a range is to combine the TRANSPOSE and QUERY functions. The TRANSPOSE function can convert rows into columns or vice-versa, while the QUERY function can execute a GOOGLE Visualization API Language query over any data range.
Steps:
- To start, select Cell E5 and then type in the following formula-
=TRANSPOSE(QUERY(TRANSPOSE(B5:D10),"",9^9))- Now, to end, press Enter key to get the required concatenated strings for the range.
Formula Breakdown
- TRANSPOSE(B5:D10)
Firstly, this TRANSPOSE function transposes the provided range B5:D10.
- QUERY(TRANSPOSE(B5:D10),””,9^9)
Afterward, the QUERY function runs a query across the transposed range. Since no query statement is provided, the QUERY function returns every entry. A large number 99 is inserted as the number of header rows to pull all the cells from a column to a single cell.
- TRANSPOSE(QUERY(TRANSPOSE(B5:D10),””,9^9))
Finally, this TRANSPOSE function amends the row returned by the QUERY function to a column.
Read More: Google Sheets QUERY Function to Concatenate Two Columns
How to Concatenate Text and Formula in Google Sheets
We may also require concatenating text and formula in a single cell. There are numerous methods to perform this operation in Google Sheets. Here, I’ll describe one of the simplest ways to concatenate text and formulas in Google Sheets. Consider the following dataset. Here, we have points earned by several participants in different rounds. We want to find the maximum point earned by a participant and show it with a text. We’ll concatenate text and a formula for this.
Steps:
- Select Cell F6 first and then type in the following concatenated formula-
=B6&" has a maximum of "&MAX(C6:E6)&" points."- After this, press the Enter key to get the required output.
- Finally, use the Fill Handle icon to copy the formula to other cells in Column F.
Things to Be Considered
- The CONCAT function can only concatenate two values.
- We need to combine the TEXT function with CONCATENATE, JOIN, or TEXTJOIN functions if we want to concatenate date and/or time with strings.
- The TEXTJOIN function can deal with blank cells while concatenating multiple cells.
Conclusion
This concludes our article to learn how to concatenate multiple cells in Google Sheets. I hope the demonstrated examples were ideal for your requirements. Feel free to leave your thoughts on the article in the comment box. Visit our website OfficeWheel.com for more helpful articles.
Related Articles
- How to Concatenate Values for IF Condition in Google Sheets
- How to Concatenate Two Columns in Google Sheets
- How to Get Opposite of Concatenate in Google Sheets (2 Ways)
- How to Concatenate Double Quotes in Google Sheets (3 Ways)
- How to Concatenate Strings with Separator in Google Sheets
- How to Concatenate in Google Sheets (6 Suitable Ways)
