Mix text and formula into a cell with a formula in an excel report
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/are the formula(s) ?
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.
Interesting Topics
-
Be successfully certified ITIL 4 Managing Professional
Study, study and study, I couldn’t be successfully certified without studying it, if you are interested...
-
Be successfully certified ITIL 4 Strategic Leader
With my ITIL 4 Managing Professional certification (ITIL MP) in the pocket, it was time to go for the...
-
Hide visual and change background color based on selection
Some small tricks to customize the background colour of a text box...
-
Stacked and clustered column chart or double stacked column chart
In excel, I use a lot the combination of clustered and stacked chart...