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-

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–

• 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–

• 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–

• Next, click Enter to get the desired output.
• Ultimately, you’ll see the joined formula and text 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–

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

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–

• Thereafter, hit the Enter button to get the result.
• Last but not least, you can see the merged formula and text 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–

• Afterward, press the Enter button to get the output.
• Finally, you’ll get the concatenated formula and text 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–

• Consequently, click the Enter button to get the desired result.
• At last, the concatenated formula and text will be 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–

• Again, tick the Enter button to get the desired output.
• In the end, we can see the joined formula and text 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–

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

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)