G'day

Not sure what the goal(s) is but go to this fantastic web site about data validations and there is 16 examples there.

http://www.contextures.com/xlDataVal02

If there is still no resolve to your challenge, post a workbook with some manual entries of before and after.

Cheers

RC