It may happen that I need to mix text and formula into a cell because I am putting every month the same thing except for a specific value. For instance, “there is an increase of XX due to some unknown outages, in total YY for this month” in which XX and YY are different values. There are 2 ways to do it, using a formula or using a macro.

When I use the formula ?

To have a general sentence with the value so each time the value is updated, the sentence is still the same except the value of course.

How to use the formula ?

The formula in this topic is with "," so depending of the operating system of your PC, the formula should have ";" instead of ",".

How is the formula ?

Putting it in the cell A1:

="There is an increase of "&ROUND(C5*100,0)&"% due to some unknown outages, in total "&SUM(C2:C4)&" for this month."

If you want to put it in 2 lines or more, you need to add “&CHAR(10)&”:

="There is an increase of "&ROUND(C5*100,0)&"% due to some unknown outages, " & CHAR(10)& "in total "&SUM(C2:C4)&" for this month."

And you need the cell to be wrapped by activating the “wrap text” option.

So let’s say that in the cell C5, I have 57% and from cells C2 to C4, numbers in which the total is 28 showing in the cell L9. The cell A1 will display this result “there is an increase of 57% due to some unknown outages, in total 11 for this month.” For the macro version, read Mix text and formula into a cell with a macro in an excel report.