Concatenate number with text but keeping in number format in Excel

I am concatenating two fields in Excel using an '&' sign, where one is a number and one text.

For example, using the formula =A1&A2 where 

cell A1 contains 'that costs '
and cell A2 contains $50,000

I want to display the sentence 'that costs $50,000'

but instead it converts the number to text to display 'that costs 50000' which is no good to me.

How can I simply set it up to display the '$' and the ','?

Answer:

=A4 & IF(A5<1000;TEXT(A5;"$#0"); TEXT(A5;"$#,##0"))

this considers numbers under $1000 and numbers between $1000 and under $1million. (supposing your are not selling items higher than $1mio otherwise you would hire a programmer and not ask here ;-))

(for formulas, depending on your country, you might have to change ; with , or the opposite

 Other excel answers

 

 

You can find similar Excel Questions and Answer hereunder

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

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

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

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

5) I want a formula to concatenate multiple cell values separated by a line break.

6) How can I set the fill color, font color and set number format of cell to date?

7) How to format variable with VBA with the very useful format function. Define the number of decimal, the way a date is formated

8) Can I apply formatting to a number concatenated with a Text?

9) How do I get the 2nd highest number in a range of numbers?

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

 

Here the previous and next chapter