validating a cell for $ amount

exceltadpole

Board Regular
Joined
Jan 14, 2004
Messages
108
Is it possible to set up a validation for cell b3 so that -
it only accepts a $ currency value
with 2 decimal places (if necessary)
examples 32 or 32.40 or 32.4
it accepts a $ sign but that is not required
once the user exits cell b3, a $ sign is also displayed in the cell
a comma is also accepted if the user enters $1,345 or 1,345.50 or 1,345.5
the user may also enter $1345 or 1345.50 or 1345.5
once the user exits cell b3, a comma is also displayed in the cell if appropriate
so the cell will only accept the characters
1234567890,.$
any other characters will generate an error message "Please enter a valid $ amount."
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,229
Curious - - why don't you just format the cell the way you want with the currency and all, and then validate for decimal in the DV dialog?
 

bydesign

Board Regular
Joined
Nov 29, 2003
Messages
184
Exceltadpole

1) Format the cell for currency
Right click on the cell / format cell / number tab / currency

2) use data valadation
Data / valadation / settings tab

change dropdown to "allow decimal", enter a min, and max value... (i.e. .01min and max 10000000000000)

on error alert tab, enter a message that you want if the user enters the wrong type of data
 

exceltadpole

Board Regular
Joined
Jan 14, 2004
Messages
108
Ok.
Once again, I did not use my brain.

I did format the cell as Currency but did not realize that further data validation would acheive the desired results.

o_O
 

Watch MrExcel Video

Forum statistics

Threads
1,127,536
Messages
5,625,390
Members
416,099
Latest member
sudarsan23

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top