How to Concatenate Text and Formula in Google Sheets (7 Ways)

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.

How to Concatenate Text and Formula in Google Sheets


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.

Using Ampersand Operator to Concatenate Text and Formula in Google Sheets

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.

Using Ampersand Operator to Concatenate Text and Formula in Google Sheets


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.

Applying CONCATENATE Function to Concatenate Text and Formula in Google Sheets

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.

Applying CONCATENATE Function to Concatenate Text and Formula in Google Sheets

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.

Assigning CONCAT Function to Concatenate Text and Formula in Google Sheets

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


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

Sajid Ahmed

Sajid Ahmed

Hi! This is Sajid Ahmed. At present, I am working as an Executive, Content Development at OfficeWheel.com. Here I will be posting articles related to different problems of Google Sheets. I have completed my graduation from Bangladesh University of Engineering and Technology in Naval Architecture and Marine Engineering. Despite a different background, I have a great interest in the research and development of data science. I try to make innovative solutions to different problems regarding Google Sheets. Please follow my blog at Officewheel.com to get different solutions regarding Google Sheets.

We will be happy to hear your thoughts

Leave a reply

OfficeWheel
Logo