# Dollar Amount

#### Zone

##### New Member
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:
(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:
For example like Please Pay Exactly\$000099Dollars and 12cents
or Please Pay Exactly\$000014500Dollars and 99cents

Z

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
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")

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

Column b

Column c

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):

In column C:

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

Let me know how that works!
A

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:

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

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.

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

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

ok so it's not pretty, but should work

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

slight modify because it error when whole dollars were involved

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

Replies
6
Views
252
Replies
1
Views
309
Replies
0
Views
1K
Replies
11
Views
26K
Replies
2
Views
249

1,196,268
Messages
6,014,344
Members
441,816
Latest member
Klingon1960

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back