format number concatenated with text in Excel

Q11. Can I apply formatting to a number concatenated with a Text?

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.

excel format number concatenated with text

excel excel format number concatenated with text

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.

excel excel excel format number concatenated with text


You can find similar Excel Questions and Answer hereunder

1) How do I change the rating numbers to rating labels?

2) Can I add a Text histogram to Table data to make it visually appealing and easy to interpret?

3) How can I find number of months that has elapsed given 2 dates?

4) Millions thousands custom number formatting in Excel

5) How can I find the number of working days between 2 dates given a holidays list?

6) Conditional formatting with if statement in Excel

7) How do I copy a Table from one location to another and retain all formulas, formats and columnwidths?

8) How can I generate random numbers between 2 limits?

9) How can I get the day number of the year for a date - for example 5 for Jan 5, 33 for Feb 2?

10) How can I clear all formats (formats alone not data) from a range of cells?

11) How can I get row count of filtered data?

12) Vba delete entire row if contains certain text in Excel

13) How do I get the rank of a number in a list of numbers?

14) In a Table , how can I add a Total Row that displays the maximum value in a column of numbers?

15) How can I have text autocomplete based on values previously entered in Column?