How to Combine Formula and Text in Google Sheets

Combining text and numbers is common in spreadsheets, for example when you want to add some context

There are a number of cases where you might need to combine text values with 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 to combine formula and text:

Using the concatenation operator (ampersand)
Using the CONCATENATE function
This Article Covers:
Using the Ampersand (&) to Combine Formula and Text
Using the CONCATENATE Function to Combine Formula and Text
How to Format Number Results when Combined with Text
Sumit
You May Also Like the Following Tutorials:
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.

Dataset to combine text and formula
Here’s how you can use the concatenation operator to combine the result of the AVERAGE function with the text “m/s”:

Click on the first cell where you want the combined values to appear (E2).
Type the formula:
=AVERAGE(B2:D2)&” m/s”.
Press the Return key.
You will find the result of the AVERAGE function combined with the text “m/s” in cell E2.Ampersand to combine formula and text
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”.Apply the formula to the entire column
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.

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.

Adding text before the formula

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 (&). The only difference is in the way both are used.

The general syntax for the CONCATENATE 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:

Dataset to combine text and formula
Below are the steps to do this: