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.

**Table of Contents**hide

## 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 **9 ^{9}** 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)**