Excel is a great tool to analyze and organize data. Sometimes we need to use a lot of formulas in Excel. By default Excel only shows the formula values in the cell. So, sometimes it may get really hard to understand what is actually going on, only by looking at the formula values. But adding extra text lines illustrating the formula values can be beneficial for all the report readers. In this regard, you will teach you to add text and formula in the same cell in Excel throughout the article using 4 relevant examples.
Example 1: Add Text and Formula in the Same Cell in Excel
Look at the following picture. An Income-Expense List has 4 columns. We have calculated the monthly savings for each of the persons in the Name column.
Now just from looking at the values, it’s quite uncomfortable to get the idea of what those values are actually about.
To increase the readability, we can add descriptive text with these values. So that any kind of reader can actually understand what those numbers are actually about.
We can merge both the descriptive texts as well as formulas to make those numbers more readable.
To add both the text and formula in the same cell in Excel,
❶ Type the following formula in cell E5 and hit the ENTER button.
=B5&"'s monthly saving is: "&C5-D5
To add both the text and formula in the same cell, you have to use the symbol, & and double inverted comma(“). So, if you want to add texts in between cell values or formulas or functions, just separate them using & and double inverted commas.
For example in the above formula, first, we have inserted cell B5. Then we have inserted the text “‘s monthly savings is: “. To separate this text line from the cell reference and formula, we have used the symbol, &.
❷ To apply the same formula for the rest of the cell, take your mouse cursor to the right-bottom corner of cell E5 and drag the Fill Handle icon up to cell E10. That’s it.
So, after adding the text and formula in the same cell, the final result will look like this:
Add Text and Formula at Any Sequence
You can add the text and formula at any sequence in the same cell. Just make sure you separate the text and formula using the symbol, & and double inverted commas (“).
For example, the following formula will add text first, and then the formula:
="Robert's monthly saving is: "&C5-D5
If you want to use the formula first and the text appears later, use the following formula:
=C5-D5 &" is Robert's monthly savings."
So, I really hope you can add text and formulas at any sequence in the same cell in Excel.
Example 2: Adding Text and Formula in the Same Cell with TEXT Function
In this example, I will show you how to add text and formula in the same cell with the Text Function in Excel.
So, let’s get started.
Without using text with the formula, the result of the Work Hour column looks like this.
Let’s add more information with the formula result, using the following steps.
❶ First type the following formula in the cell, E5, and hit the ENTER button.
=B5&"'s total work hour is: "&TEXT(D5-C5,"h")
In this formula, we have first inserted a cell, B5. Then we used a text line. To separate the text line from the cell value, B5 we have used the symbol, & and the double inverted comma (“). Finally, we have used the TEXT function with its arguments.
❷ Drag the Fill Handle icon from cell E5 to E10.
After that, the data table will look like this:
- Find and Replace a Text in a Range with Excel VBA (Macro and UserForm)
- How to Find and Replace Using Formula in Excel (4 Examples)
- Find and Replace Values in Multiple Excel Files (3 Methods)
- How to Find and Replace Asterisk (*) Character in Excel
- How to Replace Special Characters in Excel (6 Ways)
Example 3: Use of Ampersand to Add Text and Formula in the Same Cell
In this section, I’m going to show you how to add the formula with dates in the same cell in Excel.
To do so,
❶ Insert the following formula in the cell, E5.
=B5&" from "&TEXT(C5,"dd-mmm-yyyy")&" to "&TEXT(D5,"dd-mmm-yyyy")
In the above formula, first I’ve inserted cell B5 and a text. In between them, I used the symbol, & and double inverted commas, (“) to fulfill the syntax requirement. Then the TEXT function comes with its arguments.
After that another text comes which is also separated by the symbol, & and double inverted commas, (“). Then the second TEXT function comes with its corresponding arguments.
❷ Then press the ENTER button.
❸ To apply the above formula in all the cells, drag the Fill Handle icon from cell E5 to cell E10.
That’s all you need to do.
Example 4: Using CONCATENATE Function to Add Text and Formula in the Same Cell in Excel
You can use the CONCATENATE function to add text and formula in the same cell in Excel.
To do that, follow the steps below:
❶ First of all type the following formula in the cell, E5.
=CONCATENATE("Robert's monthly saving is: ",C5-D5)
❷ After that press the ENTER button.
❸ Drag the Fill Handle icon from cell E5 to cell E10 and you’re done with it.
Related Content: Excel VBA: How to Find and Replace Text in Word Document
Things to Remember
📌 To separate the text and formula, use the symbol,& and double inverted commas, (“) in between them.
Download Practice Workbook
You can download the Excel file from the following link and practice along with it.
To sum up, we have discussed adding text and formulas in the same cell in Excel with 4 relevant examples. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap. And please visit our website Exceldemy to explore more.
- How to Substitute Multiple Characters in Excel (6 Ways)
- Find and Replace from List with Macro in Excel (5 Examples)
- How to Find and Replace Multiple Words at Once in Excel (7 Methods)
- How to Find and Replace within Selection in Excel (7 Methods)
- [Fixed!] Excel Find and Replace Not Working (6 Solutions)