How To Write Text And Formula in the Same Cell in Excel

As in Excel, we always calculate this or that; the raw calculation needs to be paired with some text. The vague value itself sometimes does not make any sense. For example, in the invoice summary, stating the value does not mean anything unless you add some text like Total Sales. It is one of the best practices to add texts with formulas while drafting sales reports, invoices, and creating labels. The lucky thing is that Excel gives clarity and professionalism when using several methods.

The most beginner-friendly way is to use ampersand (&) to add text to the formula. Though the ampersand can distort the numeric formatting, it can be fixed with the TEXT function. Using both of these functions together can work miracles.

From the dataset below, you will use ampersand and TEXT to calculate all the sales of Line Total.

Steps:

➤ Select the cell in which you want to write the formula.
➤ Write the text inside the quotation marks at first, then use ‘&’ and write the formula you want. To preserve numeric formatting like currency, percentages, and decimals, you can wrap the formula with the TEXT function.

Here ‘Total Sales:’ is the text and the SUM is the formula to sum the values of cell E2 to E11. The TEXT function is used for the currency format.

➤ Press Enter to get the result with both text and formula in the output cell.

For long texts and dedicated functions, people rarely prefer the ampersand. The more flexibility, the better. This often works best with the CONCAT functions. It is the more modern approach and does the same work with a more readable and clear, concise pattern.

In this method, we will sum the Line Total of the available and join the Total Sales text using the CONCAT function.

Steps:

➤ Select the cell where you want to write the formula.
➤ Use the CONCAT function, and write the text in quotation marks followed by the formula. Also, for proper formatting, use the TEXT function.

Here ‘Total Sales: ‘ is the text and the SUM is the formula wrapped inside TEXT for numeric formatting.

➤ Press Enter to get the formula result and text in the same cell.

TEXTJOIN is basically used when you need to combine multiple texts, characters, and operators with the required formula. This can also be done with CONCAT and the ampersand operator. But this makes the entire formula messy and error-prone. TEXTJOIN keeps the formula clean and intact and also ignores the blank cells.

Here, we will combine the month name, along with total sales and total invoices. All the values will be separated by dashes (‘ —’).

Steps:

➤ Select the cell you want to get the result.
➤ Write the TEXTJOIN formula inside the cell.
➤ Inside the formula, set the delimiter, give the flag TRUE, and add usual texts and formulas with ampersand.
➤ Follow this syntax and example –

Here ‘— ‘ is the delimiter, “Month: August’, ‘Total Sales”, and “Invoices” are the text, and the SUM and COUNTA are formulas combined with the text.

➤ Press Enter to get the combined result in the selected cell.

Sometimes a single-line message seems crowded and not easy to read. Rather than using texts in multiple lines and then adding the formula, it sometimes looks better and more user-friendly. Excel has a special CHAR(10) formula that inserts a line break before the text or formula to appear the next character on a different line. It is a lifesaver as you can combine texts in multiple lines without touching any columns.

Here, we will combine the month name, along with total sales and total invoices, but in different lines for clarity.

Steps:

➤ Select the cell where you want your result to appear.
➤ Enter the formula inside the cell. Give texts inside quotation marks and formulas separated by CHAR(10) and ampersand.

where the text ‘Month: August 2025’, ‘Total Sales:’ , and ‘Invoices’ are added with the formulas of SUM and COUNTA.

➤ Press Enter to get the result.
➤ Enable Wrap Text to adjust row height to view the entire result properly.

When working with many cells and formulas, without manually writing the texts, you can create VBA Macros to do that instead. It is convenient, easier, and takes much less time if you do it correctly. Just make a custom code, and you can complete all the steps at once.

Steps:

➤ Go to the Developers tab and click on Visual Basic.

➤ This will launch the VBA editor window. Click on the Insert tab and go to Module from the menu.

➤ In the empty space, write the code below-

Sub AddTextWithTotal()
    Dim ws As Worksheet
    Dim totalSales As Double
    Set ws = ThisWorkbook.Sheets("VBA Macros") ' Change to your sheet name
    totalSales = Application.WorksheetFunction.Sum(ws.Range("E2:E11"))
    ws.Range("D13").Value = "Total Sales for August: " & Format(totalSales, "$#,##0")
End Sub

➤ Click on  Ctrl  +  S  to save the code snippet and close the VBA window.
➤ Now, choose the Macros from the Developer tab.

➤ In the Macros window, choose the name of the formula you just created. In this example, it is  AddTextWithTotal.

➤ Click the Run button to apply the formula in the desired cell.

Power Query is another great option for preparing texts with formula results. It does not rely upon an external formula nor a VBA customized formula. It gives a clear, static result of the data in an external worksheet without leaving formulas in the original cells.

Steps:

➤ Select any cell and go to the Data tab -> From Table/Range.

➤ Mark the box – My table has headers.

➤ In the Power Query Editor window, go to the Home tab and choose Group By.

➤ Select Advanced and remove the Group By column name.
➤ Give the new column name Total Sales, Operation name Sum (or as you require), and choose the column Line Total (name of the column you want to use the formula).

➤ Click OK to make a new column with the total sales value.

➤ Now, go to the Add Column tab and click Custom Column.
➤ In the Custom Column window, give a name to the column you are creating. And then, write the formula below in the formula box

➤ Click OK to generate a column with the text and formula of the total sales.

➤ Remove the unnecessary columns like Total Sales by right-clicking the column and selecting Remove.

➤ Click on Close and Load in the Home Tab to export this new column to a separate worksheet.

Sometimes the output cells need to display different texts based on different values. In that case, you may need to use conditions like IF with the text and the formulas. It might sound hard, but it is actually a lot easier. And you can do this with a single, simple formula line.

Here, we will display the text ‘Target Met’ if the sum of the total sales exceeds $10,000 or ‘On Track’ if they do not.

Steps:

➤ Select the cell to display the output.
➤ Enter the formula with the IF and texts –

Here, IF ensures that if the SUM values are greater than 10,000, it will show ‘Target Met’ and otherwise ‘On Track’, and ampersand is used to join the texts.

➤ Press Enter to get the output in the selected cell.
➤ Add more conditions if required with IFS.

Frequently Asked Questions (FAQs)

Can I type text and still see the formula’s live result in the same cell?

You can see the texts and the formula in the same cell, but you can type simply the text into the live formula cell and expect it to work fine. You need to use CONCAT or an ampersand to add the text to the formula.

Why did my summed result stop working after I added text to the formula?

After adding text to the summed result, the output changes to text instead of numeric values. As a result, it can’t be used further to modify the calculations or dynamically update them.

How do I format percentages when combining with text?

To format the percentages in their correct presentation, you need to use the TEXT function and write the main formula inside it. For the percentage, you need to add ‘0.00%’ after the formula.

TEXT((SUM(E2:E11)*10), 0.00%)

Can I use cell references inside the text part?

For the text part, you can also use the cell reference instead of writing the text manually. However, make sure the text appears completely correct in the referenced cell. Ampersand syntax is used to apply the cell references “&A1&”.

How can all text-formula combinations be updated automatically after a data change?

For manual formulas like ampersand, CONCAT, or TEXTJOIN, the text data is automatically changed when updated in the formula. However, for the VBA Macros and Power Query, you should refresh or reload the data again to see the changes.

Concluding Words

Combining texts with the formula results in the same cell makes your data more readable and informative. With the range of Excel tools at hand, from basic concatenating methods to multi-line layouts, conditional messages, Power Query transformation, and VBA automation, you can choose the best that aligns with your needs. Just go through each method, download our sample workbook, and have hands-on experience with each of them. Get ready for the data that not only calculates correctly but also communicates clearly!