• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 199
  • Last Modified:

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
0
rayluvs
Asked:
rayluvs
  • 3
  • 2
4 Solutions
 
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now