How to Concatenate Text and Formula in Google Sheets

Concatenating text and formula in Google Sheets means joining words, numbers, or cell values together into one line of text using a formula. It can be useful when you want to create summaries or custom messages based on your data. If you’re working with customer names, order details, or totals, combining them into a single sentence makes the information easier to read.

In this article, we’ll show you how to concatenate text and formula in Google Sheets using simple steps that help you save time and make your spreadsheet more useful.

In the following dataset, there are three columns labeled Customer, Items Ordered, and Total Cost. Column A contains customer names, Column B lists the number of items each person ordered, and Column C shows the total cost of the items. Column D is currently empty and will be used to display the combined summary using a formula.

We’ll use this dataset to apply and practice the methods for concatenating text and formula in Google Sheets.

The simplest way to combine text with cell values in Google Sheets is by using the & operator. Using this method you join words, spaces, and values into a single sentence inside one cell.

Here’s step-by-step guide to apply this method:

➤ Open your dataset in Google Sheets and go to the column where you want to create the summary. For example, let’s select Column D.

➤ Select cell D2, and enter the following formula

➤ Press Enter.

➤ Now, you’ll see a sentence like this: Alice ordered 5 items worth $125.

➤ To apply this formula to other rows, click the small square at the bottom-right corner of cell D2 and drag it down to fill the rest of the column.

Another way to combine text and values in Google Sheets is by using the TEXTJOIN function. This function joins multiple pieces of text or cell values into one string, with a delimiter you choose, such as a space or comma.

Here’s a simple step-by-step guide how to use this method:

➤ Open your Google Sheets file and go to Column D, where you want the combined summary.

➤ Select cell D2, and enter this formula

➤ Now, you’ll see the concatenate text like this: Alice ordered 5 items worth $125.

➤ To fill the rest of the rows with similar summaries, drag the fill handle from cell D2 down to other rows.

You can also use the CONCATENATE function to join text and cell values in Google Sheets. This function takes multiple arguments and combines them into one continuous string.

Here’s how to apply the CONCATENATE function:

➤ Open your Google Sheets file and go to Column D, where you want the combined summary.
➤ Select cell D2, and enter this formula

➤ Press Enter.

➤ To apply this formula to other rows, drag the fill handle from cell D2 down through the column.

Frequently Asked Questions

How to combine a formula and text in Google Sheets?

You can use the CONCATENATE function to join text and formulas in one cell. Here’s how to do that:
➤ Select the cell where you want the result.
➤ Enter a formula like this:
=CONCATENATE(A2, ” bought “, B2, ” items for $”, TEXT(C2, “#,##0”))
➤ Press Enter to see the combined sentence.
➤ Use the fill handle to copy the formula to other rows.

Why should I use the TEXT function when concatenating?

The TEXT function helps format numbers so they look neat when combined with text. It adds commas, decimal points, or currency symbols to numbers. Also, it makes the final sentence easier to read. Without TEXT function, numbers might show too many decimals or appear unformatted.

Wrapping Up

Combining text and formulas in Google Sheets helps you create clear and dynamic summaries without typing everything manually. When you work with data like names, numbers, or dates, joining them into one sentence makes your spreadsheet easier to understand.

Using simple formulas like the Ampersand (&) operator, CONCATENATE, or TEXTJOIN, along with the TEXT function for formatting, saves time and reduces errors. Try these methods to make your spreadsheets more manageable and organized.