How can we format a value to Currency when used in a concatenate string

We have an Excel 2007 sheet that has a series of values in Currency and Percentages.  We are writing sentences and concatenating these values.  

The problem is that the value is displayed unformatted.

How can we display the value formatted when in a concatenate string?

See the file attached.  The cell has a format of $1,500.00, but when used in a sentences and the value concatenated, it displays 1500.

FormatCurrency.withString.xlsx
rayluvsAsked:
Who is Participating?
 
AlanConsultantCommented:
Hi,

Try something like this:

A1 = 1500 (but formatted to display as $1,500)

A2 = "You owe me "&Text(A1,"$#,##0")

HTH,

Alan.
0
 
AlanConsultantCommented:
For the percentages:

A1 = 0.15 (but formatted to display as 15%)

A2 = "I am "&Text(A1,"0%")&" better than before."


HTH,

Alan.
0
 
rayluvsAuthor Commented:
The percentage work, but in the currency, how do we include the cents?
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
HutsonAssociatesITCommented:
A2 = "You owe me "&Text(A1,"$#,##0.00")
0
 
AlanConsultantCommented:
Hi,

Try this:

A2 = "You owe me "&Text(A1,"$#,##0.00")


The format within the text function is exactly the same as you have in your formatting for the cell.  If you choose 'custom' under the cell format options, it will show you the string you are actually using (even if you chose one of the 'preset' options).

HTH,

Alan.
0
 
rayluvsAuthor Commented:
thanx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.