How to Combine Formula and Text in Google Sheets (2 Easy Ways)

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

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”:

  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.Ampersand to combine formula and text
  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”.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:

  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.Concatenate formula to combine text and formula
  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”.Apply the formula to the entire column

Explanation of the Formula

The CONCATENATE function in this example 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 you formula would be:

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

Adding text before formula concatente

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. 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:

Character Description
0 Represents a digit in the number. An insignificant 0 will appear in the results.
# Represents a digit in the number. An insignificant 0 will not appear in the results.
? Represents a digit in the number. An insignificant 0 will appear as a space in the results.
$ Formats the number as a dollar value.
.(period) Formats the number with a decimal point.
,(comma) Formats the number with a thousand separator.
/ Formats the number as a fraction.
% Formats the number as a percent
“text” Adds text to the formula. Insert the desired text within quotations for it to appear.
@ Displays text entered into a cell.
* Repeats the following character to fill in the remaining space in the cell.
_ (underscore) Adds a space equal in width to the following character.

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

TEXT formula to format the number part

In this tutorial, I showed you two easy ways to combine a formula with text in Google Sheets.

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.

I hope you found this tutorial helpful.

Other Google Sheets tutorials you may also like: