Format number concatenated with text in Excel
For example, I want to get a summary text "Actual vs Target difference for Barbara Schmidt is $3'769" for the table below. Note the formatting for dollar value.
To do it in Excel, here is the answer:
a) Enter the formula ="Actual vs Target difference for " & C5 & " " & D5 & " is " & TEXT(I5,"$#,##0")
The last element in the formula TEXT(I5,"$#,##0") sets the format of the number found in cell I5. Even though string operation is performed on the cell, the number in the string is formatted as needed.