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 macro or using a formula.

When I use the macro ?

Each time that the excel is saved, to display the day and the name of the person who does it.

How to create the macro ?

Read How to create, edit, hide and select a macro in an excel report

How to create the button to associate it with the macro ?

Read How to create a button and associated it to a macro in an excel report

How is the macro ?

Copy the code below and paste it into your macro. You will see my comments in green if exist so follow the help to adapt to your need.

Sub test()
Dim i As String, j As String
' change the cell reference B1 and B2
i = Range("B1")
j = Range("B2")
' change the cell D1 and the words between “”
Range("D1").Value = "there was a total of " & i & " problem tickets generating " & j & " change requests for this month."
End Sub

So let’s say that in the cell B1, I have 47 and for B2 25. The cell D1 will display this result “there was a total of 47 problem tickets generating 25 change requests for this month.”

Usually I don’t use macro for that, using the formula verion is enough and simpler (read Mix text and formula into a cell with a formula in an excel report) so why I am explaining you the macro option. I use the macro if I need for instance to display the day and why not, who is the last person who does it.

Sub test()
' change the cell D2 and the words between “”
Range("D2").Value = "Last update at " & Format(Now, "DDD DD/MM/YYYY HH:MM") & " by " & Application.UserName
End Sub

The result in the cell A1 will be “Last update at Wed 20.02.2019 14:57 by ME”. If you want to put “by ME” in a new line, the macro is:

Sub test()
' change the cell D3 and the words between “”
Range("D3").Value = "Last update at " & Format(Now, "DDD DD/MM/YYYY HH:MM") & vbNewLine & "by " & Application.UserName
End Sub