Example 1 – Add Text and Formula in the Same Cell in Excel with the Ampersand Operator
We have an Income-Expense List has four columns. We have calculated the monthly savings for each person.
To increase the readability, we can add descriptive text with these values by merging the descriptive texts and formulas.
- Insert the following formula in cell E5 and hit the Enter button.
=B5&"'s monthly saving is: "&C5-D5To add both the text and formula in the same cell, you have to use the ampersand symbol & and a double quote(“). The ampersand concatenates values into strings, and double quotes turn the text inside into string values.
- Hover over the bottom-right corner of cell E5 and drag the Fill Handle icon down to cell E10.
Here’s the result.
Add Text and Formula at Any Sequence
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."
Example 2 – Adding Text and Formula in the Same Cell with the TEXT Function
Without using text with the formula, the result of the Work Hour column looks like this. We’ll add a bit of text to the result cells.
- Use the following formula in cell E5 and hit the Enter button.
=B5&"'s total work hour is: "&TEXT(D5-C5,"h")The TEXT function converts the value inside into a text value in the specified formatting. In this example, it returns the hours value of D5-C5 as a single number.
- Drag the Fill Handle from cell E5 to E10.
Read More: How to Add Text in IF Formula in Excel
Example 3 – Use Ampersand to Add Dates and Formula in the Same Cell
- Insert the following formula in the cell E5.
=B5&" from "&TEXT(C5,"dd-mmm-yyyy")&" to "&TEXT(D5,"dd-mmm-yyyy")This formula uses the TEXT function to return the dates from C5 and D5 into the required textual format.
- Hit Enter.
- Drag the Fill Handle down to fill the column.
Read More: How to Add Text Before a Formula in Excel
Example 4 – Using the CONCATENATE Function to Add Text and Formula in the Same Cell in Excel
- Insert the following formula in E5.
=CONCATENATE("Robert's monthly saving is: ",C5-D5)- Hit Enter and drag the Fill Handle icon from cell E5 to cell E10.
Download the Practice Workbook
Related Articles
- How to Add Text to Cell Value in Excel
- How to Add Text in Excel Spreadsheet
- How to Add Text to Beginning of Cell in Excel
- How to Add Text to Multiple Cells in Excel
- How to Add Text to Cell Without Deleting in Excel
- How to Add Text in the Middle of a Cell in Excel
- How to Add Text to End of Cell in Excel
- How to Add a Word in All Rows in Excel
<< Go Back to Concatenate Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
From your lesson: After that another text comes which is also separated by the symbol, & and double inverted commas, (“).
REALLY? DOUBLE Inverted COMMAS? How do you get an inverted comma? Kinda looks like a QUOTE to me.
I’m very much looking forward to an explanation of “double inverted commas”.
Sincerely,
Larry Kanzia
[email protected]
Hi Larry Kanzia,
Inverted commas can be single – ‘x’ – or double – “x”. They are also known as quotation marks, speech marks, or quotes.
You can get a single inverted comma just by pressing the comma button next to the ENTER button on your keyboard. To insert a double inverted comma, press and hold the SHIFT key, then press the comma key next to the ENTER button.
Thanks!