The Easiest Google Sheets Add Text to Formula Guide [Step-by-Step]

How do you add text to formulas in Google Sheets? We’re glad you asked. Combining text and numbers is common in spreadsheets, for example when you want to add some context to shared sheets. In this article, which we’re calling our “Google Sheets Add Text to Formula Guide”, we go in depth on the subject.

That mean’s we will specifically talk about how to use the CONCATENATE formula and operator to combine text and formulas in Google Sheets.

Reasons Why You Would Need Google Sheets Add Text to Formula Functions

There are a number of cases where you might need to combine text values with the results of formulas in Google Sheets. Here are a few cases:

• You might need to aggregate values from a spreadsheet and combine the result with some text so that it is more easily understandable.
• You might want to add a unit with a number obtained from a formula, for example, 15kwH or 5 m/s
• You might want to combine text and formula values from two or more separate cells into a single cell.

The process of combining text with formula in Google Sheets merely requires you to use a concatenation operator or function.

In this tutorial we will look at two different ways Google Sheets can add text to a cell:

• Using the concatenation operator (ampersand)
• Using the CONCATENATE function

Using the Ampersand (&) to Combine Formula and Text

The concatenation operator is denoted by the ampersand symbol (&). It is used to combine given values together and it returns a string that contains the values appended to one another.

As such, this operator provides a great way to combine formula and text into a single cell value. Let us assume you have the following dataset and want to display the average speed in m/s for each row.

Add Text After the Formula With the Concatenation Operator

Here’s how Google Sheets can add text after a formula using the concatenation operator to combine the result of the AVERAGE function with the text “m/s”:

1. Click on the first cell where you want the combined values to appear (E2).
2. Type the formula:

   =AVERAGE(B2:D2)&” m/s”.

3. Press the Return key.
4. You will find the result of the AVERAGE function combined with the text “m/s” in cell E2.
5. Double click on the fill handle at the bottom right corner of cell E2. This will copy the formula to all the other cells of column E, so you get individual average speeds for each row, combined with the text “m/s”.

Explanation of the Formula

The concatenation operator (&) appends the value on the right of it to the value on the left. In this case, it appended the string “m/s” to the AVERAGE formula result for each row.

How to Add Text Before the Formula With the Concatenation Operator

Now, what if you wanted some text before the AVERAGE formula result too? For example, what if you wanted to say “The average speed is 24.5 m/s”?

In that case, you simply need another concatenation operator before the AVERAGE function, as follows:

=”The average speed is “&AVERAGE(B2:D2)&”m/s”.

Remember to enclose any string or text value within double-quotes.

You can use the concatenation operator to combine multiple substrings and values into one. All you need to do is join each pair of substrings with the ampersand operator (&) between them.

Using the CONCATENATE Function to Combine Formula and Text

The CONCATENATE() function provides the same functionality as the concatenation operator (&) to add text to a formula in Google Sheets. The only difference is in the way both are used.

The general syntax for the CONCATENATE Google Sheets text and formula in same cell function is:

=CONCATENATE(text1, [text2],…)

Where text1, text2, etc. are substrings that you want to combine together.

You can use this function to combine multiple substrings and values into one, where each substring is a parameter of the function.

Let’s apply the CONCATENATE function to the same dataset as the one shown in Method 1:

Below are the steps to do this:

1. Click on the first cell where you want the combined values to appear (E2).
2. Type the formula: =CONCATENATE(AVERAGE(B2:D2),” m/s”).
3. Press the Return key.
4. You will find the result of the AVERAGE function combined with the text “m/s” in cell E2.
5. Double click on the fill handle at the bottom right corner of cell E2. This will copy the formula to all the other cells of column E, so you get individual AVERAGE speeds for each row, combined with the text “m/s”.

Explanation of the Formula

The CONCATENATE function allows Google Sheets to append text in this example. It takes the first parameter and joins the second parameter to the end of it. In this case, it took the result of the AVERAGE function and joined the text “m/s” at the end of it for each row.

If you also wanted some text to be added before the AVERAGE formula result, as in “The average speed is 24.5 m/s”, then simply add the text “The average speed is “ as the first parameter of the formula used above. So your formula would be:

=CONCATENATE(”The average speed is “,AVERAGE(B2:D2),”m/s”)

How to Format Number Results when Combined with Text

From the results of both the above methods, you will notice the results of the average functions don’t exactly look very appealing. The results would perhaps have been easier to read if they were formatted and rounded to two decimal places.

Normally you could simply use the Custom Formatting functionality to convert the formula result to your required format. However, this is not so easy when you have a cell that has a combination of formula and text.

Fortunately, there is a way to format the results we obtained in column E after you use Google Sheets to concatenate text and formulas. We can do so by using the TEXT function.

The TEXT function is used to convert a number to text according to a specified format. The syntax for the function is:

=TEXT(number, format)

Here,

• number is the number, date, or time that you want to format
• format is a string specifying how you want the number to be formatted.

Note that the format parameter should always be enclosed in double-quotes since it is a string.

For example, if you want to format the number 13.5431 to 2 decimals places, you pass it through the TEXT function as follows:

=TEXT(13.5431,”#.##”)

The second parameter ”#.##” means we want the format of the number to be limited to only 2 decimal places.

An insignificant 0 will not appear in the result. The ‘#’ symbol in this formula is called a syntax character and is quite often used when formatting numbers. Here are some other commonly used syntax characters:

To apply formatting to numbers that are displayed with text in column E of our example, you could incorporate the TEXT function into the formula as follows:

• =TEXT(AVERAGE(B2:D2),”#.##”)&” m/s”, if using the concatenation operator
• =CONCATENATE(TEXT(AVERAGE(B2:D2),”#.##”),” m/s”) if using the CONCATENATE function

Adding Text to Formulas in Excel

For those who use Excel, you also have the ability to add text to formulas. Check out Excel’s concatenate function in Microsoft’s official documentation. Note that the formula changed in the most recent versions. Those using Excel 2016 or the web versions will want to check out the CONCAT function instead.

Google Sheets Combine Text and Formula FAQ

How Do I Add Text to a Cell in a Formula Sheet? / How Do I Have Text and Formula in the Same Cell?

You can use the concatenate operator (&) to add text to a cell with a formula. You must also put the text in quotation marks. For example the formula =SUM(5,5)& “Ten” would show 10 Ten as the result.

Can You Add Text After a Formula in Sheets? / How Do You Add Text After a Formula?

Yes you can add text after a formula in Google Sheets. You just have to use the & symbol after the function followed by the desired text in quotation marks after the formula, for example:

 =FORMULA(8,8)& "Text Here"

Wrapping up the Google Sheets Text and Formula in the Same Cell Guide

In this Google Sheets add text to formula guide, I showed you two easy ways to concatenate data.

I also showed you how you can format the numbers obtained as a result of the formulas so that the combined text is easily readable and understandable. Mastering the Google Sheets add string to formula concatenation function is the easiest way to add text to formula, but learning the CONCATENATE formula will allow you to tackle more difficult formulas down the track so make sure you experiment with both.

Related Reading:

• How to Convert Text to Numbers In Google Sheets
• How to Convert Text to Date in Google Sheets
• How to Copy Text From a Picture [3 Easy Ways]
• How to Split Text to Columns in Google Sheets (with Examples)
• How to Use the ISTEXT Function in Google Sheets (Easy)
• How to Merge Cells in Google Sheets