We are aware of concatenating 2 corresponding cells in Google Sheets. But to concatenate text and formula is pretty straightforward. For this purpose, we can use several functions like the **CONCATENATE function**, operators like the **Ampersand Operator (&) **and many more. In this article, we’ll see 7 suitable methods to concatenate text and formula in Google Sheets with clear images and steps.

**A Sample of Practice Spreadsheet**

You can download Google Sheets from here and practice very quickly.

**7 Suitable Methods to Concatenate Text and Formula in Google Sheets**

Let’s get introduced to our dataset first. Here we have some transports in **Column B** and their speeds in **Column C**. Moreover, we have applied the following formula in **Cell C11** to determine their average speed-

`=AVERAGE(C5:C9)`

The unit of the speed is “m/s” which we want to add in **Cell C11** with the remaining formula. So, I’ll show you 7 suitable methods to concatenate text and formula in Google Sheets by using this dataset.

**1. Using Ampersand Operator**

We can use the **Ampersand Operator (&) **to concatenate **text and formulas in Google Sheets**. This operator simply connects the numerical value obtained by any formula with texts. We can add text both after and before the given formula. Here we are using **the AVERAGE function**. Let’s see the steps.

**1.1 Concatenating Text After Formula**

Before all, we’ll see how to concatenate text after formula with space in Google Sheets. We’ll put a space into the **Apostrophe Sign (“”)** before the text “m/s” because we want a space between the formula and text values in our output.

**Steps:**

- Firstly, type the following formula in
**Cell C11**–

`=AVERAGE(C5:C9)&" m/s"`

- Secondly, hit
**Enter**to get the output. - Finally, you will see the concatenated formula and text in
**Cell C11**.

**Read More: How to Add Space with CONCATENATE in Google Sheets**

**1.2 Concatenating Text Before Formula**

Now, we’ll extend our previous method by adding text before the formula. I’ll show how to concatenate text before the formula with a **Separator Sign (:)** in Google Sheets. Here, I am adding the text “Average Speed of Transports is: ” before the formula. As you can see we have inserted a **Separator Sign (:)** to make the text separated from the formula. Let’s see how to do it.

**Steps:**

- At first, write the following formula in
**Cell B12:C12**–

`="Average Speed of Transports is: " &AVERAGE(C5:C9)&" m/s"`

- Then, press
**Enter**to get the result. - At last, you can find the concatenated formula and text in
**Cell****B12:C12**.

**2. Applying CONCATENATE Function**

Unlike the previous method, we can apply **the ****CONCATENATE function** to join text and formula in Google Sheets. This function joins any strings text or numbers quickly. Also, we can add text both before and after the formula. We’ll see both methods below.

**2.1 Joining Text after Formula**

First, we’ll see how to join text after formula by using the **CONCATENATE **function.

**Steps:**

- First of all, insert the following formula in
**Cell C11**–

`=CONCATENATE(AVERAGE(C5:C9)," m/s")`

- Next, click
**Enter**to get the desired output. - Ultimately, you’ll see the joined formula and text in
**Cell****C11**.

**Formula Breakdown**

**AVERAGE(C5:C9)**

Firstly, this function determines the average speed of the values from **Cells C5** to **C9**.

**CONCATENATE(AVERAGE(C5:C9),” m/s”)**

Then, this function concatenates the text “m/s” after the value obtained by the **AVERAGE **function in **Cell C11**.

**2.2 Joining Text before Formula**

Now, we’ll see how to join any text before the formula by applying the **CONCATENATE **function alone.

**Steps:**

- In the first place, put the following formula in
**Cell B12:C12**–

`=CONCATENATE("Average Speed of Transports is: " ,AVERAGE(C5:C9)," m/s")`

- After that, tick
**Enter**to get the desired result. - In the end, the joined formula and text will be in
**Cell****B12:C12**.

**Formula Breakdown**

**AVERAGE(C5:C9)**

At first, this function gives the average speed of the values from **Cells C5** to **C9**.

**CONCATENATE(“Average Speed of Transports is: ” ,AVERAGE(C5:C9),” m/s”)**

Then, this function concatenates the text “Average Speed of Transports is: ” before the value obtained by the **AVERAGE **function in **Cell ****B12:C12**. And also joins the text “m/s” after the value.

**Read More: ****How to Concatenate Number and String in Google Sheets**

**3. Assigning CONCAT Function**

Further, we can use **the ****CONCAT function** instead of the **CONCATENATE **function to connect text and formulas in Google Sheets. This is the simpler version of the **CONCATENATE **function but works the same.

**Steps:**

- In the beginning, type the following formula in
**Cell C11**–

`=CONCAT(AVERAGE(C5:C9)," m/s")`

- Thereafter, hit the
**Enter**button to get the result. - Last but not least, you can see the
**merged formula and text**in**Cell****C11**.

**Formula Breakdown**

**AVERAGE(C5:C9)**

First of all, this function calculates the average speed of the values from **Cells C5** to **C9**.

**CONCAT(AVERAGE(C5:C9),” m/s”)**

Then, this function joins the text “m/s” after the formula in **Cell C11**.

**Similar Readings**

**How to Concatenate Two Columns in Google Sheets****How to Get Opposite of Concatenate in Google Sheets (2 Ways)**

**4. Using JOIN Function**

We can also use **the ****JOIN function** to concatenate text and formula. The advantage of this function is that we can define the value we want to put in the middle of the text and formula if we use this function. Here the value is space. That’s why we define it earlier in the **JOIN **function and we don’t have to put any space inside the **Apostrophe Sign (“”).**

**Steps:**

- Before all, write the following formula in
**Cell C11**–

`=JOIN(" ",AVERAGE(C5:C9),"m/s")`

- Afterward, press the
**Enter**button to get the output. - Finally, you’ll get the concatenated formula and text in
**Cell****C11**.

**Formula Breakdown**

**AVERAGE(C5:C9)**

Firstly, this function produces the average speed of the values from **Cells C5** to **C9**.

**JOIN(” “,AVERAGE(C5:C9),”m/s”)**

Then, this function adds the text “m/s” after the value obtained by the formula in **Cell C11**.

**5. Applying TEXTJOIN Function**

**The TEXTJOIN function** also works like same as the **JOIN **function. We can define the value or space we want in between the text and formula separately.

**Steps:**

- Earlier on, insert the following formula in
**Cell C11**–

`=TEXTJOIN(" ",,AVERAGE(C5:C9),"m/s")`

- Consequently, click the
**Enter**button to get the desired result. - At last, the concatenated formula and text will be in
**Cell****C11**.

**Formula Breakdown**

**AVERAGE(C5:C9)**

At first, this function returns the average speed of the values from **Cells C5** to **C9**.

**TEXTJOIN(” “,,AVERAGE(C5:C9),”m/s”)**

Then, this function connects the text “m/s” after the formula in **Cell C11**.

**Read More: How to Concatenate Values for IF Condition in Google Sheets**

**6. Combining TEXT Function with Ampersand Operator**

At this moment we’ll combine **the TEXT function** with the **Ampersand Operator (&) **to concatenate text and formula. The **TEXT **function converts any numerical values into text format and then the **Ampersand Operator (&) **connects the 2 texts easily.

**Steps:**

- Before, put the following formula in
**Cell C11**–

`=TEXT(AVERAGE(C5:C9),"#.##")&" m/s"`

- Again, tick the
**Enter**button to get the desired output. - In the end, we can see the joined formula and text in
**Cell****C11**.

**Formula Breakdown**

**AVERAGE(C5:C9)**

Firstly, this function gives us the average speed of the values from **Cells C5** to **C9**.

**TEXT(AVERAGE(C5:C9),”#.##”)&” m/s”**

Then, the **TEXT **function converts the value obtained by the **AVERAGE **function into a text format. Finally, with the help of the **Ampersand Operator (&)**, we join the text “m/s” after the formula in **Cell C11**.

**Read More: How to Append Text in Google Sheets (An Easy Guide)**

**7. Merging RIGHT and LEFT Functions with Ampersand Operator**

In addition to that, we can merge the **RIGHT** and **LEFT** functions with the **Ampersand Operator (&) **to join text both before and after the formula in Google Sheets. The **RIGHT** and **LEFT** functions bring out any part of the value from the main value for a given position and place them before and after the values respectively. Then the **Ampersand Operator (&) **connects the texts with the formula.

**Steps:**

- Initially, type the following formula in
**Cell B12:C12**–

`="Average Speed of Transports is: "& RIGHT(LEFT(AVERAGE(C5:C9),4)&" m/s",8)`

- Moreover, hit
**Enter**to get the result. - Ultimately, you’ll see the text joined before and after the formula in
**Cell****B12:C12**.

**Formula Breakdown**

**AVERAGE(C5:C9)**

Firstly, this function gives us the average speed of the values from **Cells C5** to **C9**.

**LEFT(AVERAGE(C5:C9),4)&” m/s”**

Next, the **LEFT **function returns the value obtained by the **AVERAGE **function and connects the text “m/s” after the formula with the help of the **Ampersand Operator (&).**

**“Average Speed of Transports is: “& RIGHT(LEFT(AVERAGE(C5:C9),4)&” m/s”,8)**

Then, the **RIGHT **function does the same task and adds the text “Average Speed of Transports is: ” before the whole formula.

**Conclusion**

That’s all for now. Thank you for reading this article. In this article, I have discussed 7 suitable methods to concatenate text and formula in Google Sheets. Please comment in the comment section if you have any queries about this article. You will also find different articles related to google sheets on our **officewheel.com****.** Visit the site and explore more.

## Related Articles

**How to Concatenate Double Quotes in Google Sheets (3 Ways)****Google Sheets QUERY Function to Concatenate Two Columns****How to Concatenate Strings with Separator in Google Sheets****Concatenate in Google Sheets (6 Suitable Ways)****How to Concatenate If Cell Is Not Blank in Google Sheets (7 Ways)****Concatenate With Separator in Google Sheets (3 Ways)****How to Concatenate Strings in Google Sheets (2 Easy Ways)****Concatenate Multiple Cells in Google Sheets (11 Examples)**