Dollar Amount

[Zone]

Hi all,

How can I display the $xx.xx into a statement?

I have these $ amounts in column A:
99.12
107.65
14500.99

Have the result in column B as:
Please Pay Exactly$00000xx.yy
(where xx is tenth, hundreds, thousands and yy is the cent)
00000 is to prevent forgery

and to have the results in column C using same column A:
Please Pay Exactly$0000xxDollars and yycents
For example like Please Pay Exactly$000099Dollars and 12cents
or Please Pay Exactly$000014500Dollars and 99cents

Thank you for your help!
Z

 

[Joe Was]

If A1 has the amount, then in another cell use:

="Please Pay this Amount: " & DOLLAR(A1,2)

or

="Please Pay this Amount: " & TEXT(A1, "$0,000,000.00")

 

[jearl97]

Here's another option since you want to keep the zeros:

Column b
="Please Pay Exactly$00000"&A1

Column c
="Please Pay Exactly$0000"&TRUNC(A1,0)&"Dollars and "&RIGHT(A1,2)&"cents"

 

[jearl97]

Z,

I got your PM and I replied, but I thought I'd post my reply here as well.

I think this may be what you're looking for (It seems Joe and I were both on the right track):


Since you always want to keep 9 digits after the dollar sign,

In column B (assuming you're starting with the first row):

="Please Pay Exactly "&TEXT(A1, "$000000.00")


In column C:

="Please Pay Exactly "&TEXT(A1, "$000000")&" Dollars and "&RIGHT(A1,2)&" cents"


Let me know how that works!
A

 

[Scott Huish]

I don't think you can use RIGHT(A1,2) to get the cents, because what if the value in A1 was a whole number like 99?

In B1:
="Please Pay Exactly"&TEXT(A1,"$000000000.00")

In C1:
="Please Pay Exactly" & TEXT(A1,"000000000") & "Dollars and " & ROUND(MOD(A1,1),2)*100 & "cents"

 

[texasalynn]

In C1:
="Please Pay Exactly" & TEXT(A1,"000000000") & "Dollars and " & ROUND(MOD(A1,1),2)*100 & "cents"

this is going to make the dollars round up, so for the OP second example your will get
108 dollars and 65 cents instead of 107 dollars and 65 cents.

 

[jearl97]

Good point HOTPEPPER. I was assuming the cell was formatted to show 2 decimal places and it was the first solution to come to mind. I should have thought that one through a little more

texasalynn, anyone,

Are there any other suggestions? Or would it be okay for Zone to use 'right' then?

A

 

[jearl97]

After checking, if cell ends in .00, using right will not work unless column A is set to 'text'

 

[texasalynn]

ok so it's not pretty, but should work

=IF(MID(A1,(FIND(".",A1)),1)=".",
      "Please Pay Exactly "&TEXT(LEFT(A1,(FIND(".",A1))),"0000000")
          &"Dollars and "&ROUND(MOD(A1,1),2)*100&"cents",
           "Please Pay Exactly" & TEXT(A1,"000000000") & "Dollars and "
             & ROUND(MOD(A1,1),2)*100 & "cents"

 

[texasalynn]

slight modify because it error when whole dollars were involved

=IF(ISNUMBER(MID(A1,(FIND(".",A1)),1)),"Please Pay Exactly "
    &TEXT(LEFT(A1,(FIND(".",A1))),"0000000")&"Dollars and "
      &ROUND(MOD(A1,1),2)*100&"cents","Please Pay Exactly" 
          & TEXT(A1,"000000000") & "Dollars and " 
            & TEXT(ROUND(MOD(A1,1),2)*100,"00") & "cents")