Excel: Mix text and formulas in one cell
Texts and formulas can be linked in an Excel cell thanks to the "&" operator and the "TEXT" function. For example, amounts in invoices can be directly integrated into the text with little effort.
Merge formula and text into one Excel cell
- In the text field you have to enclose the desired formula with quotation marks and the "&" sign.
- To insert the invoice amount and tourist tax directly into a text field, enter this formula: & TEXT (C1; "0.00") & "EUR are 5% tourist tax (" & TEXT (C1 / 105 * 5; "0.00 ") &" EUR) included. " (see screenshot). In this example, the invoice amount is in cell C1.
- Under no circumstances should you forget the "TEXT" function, as this only rounds the result from 13 to two decimal places.
- Now you will receive the text based on the example: "5% tourist tax (EUR 119.05) is included in the invoice amount EUR 2500.00."
This guide article refers to MS Excel 2010. Further helpful tips and tricks around MS Excel can be found here.