[SalesForce] Validation rule to allow number, decimal and percentage

I am trying to write a validation rule which will allow only numbers, decimal and % symbol.

Below are the samples i am trying but it is not working. If I put 17.5% it is not working

  • Any number upto 100 (0-100)
  • Number with % symbol = 0% to 100%
  • Decimal = 0.00% to 100.00%

!REGEX(Text_Field__c , "[0-9]+(.[0-9])+[%]")

Best Answer

In your regex, you're looking for one or more digits (any number of them), followed by one or more instances of any single character (the period) and a digit, followed by a required percent sign. 17.5% should pass - I'm not sure why it's not. But so would 67534&8(9h6%. I think you should separate the max value part from the regex part. Try this:

!REGEX(Text_Field__c, "[0-9]{1,3}(\\.[0-9]{1,2})?%?") || 
    IF(RIGHT(Text_Field__c,1) = "%", 
        VALUE(LEFT(Text_Field__c,LEN(Text_Field__c)-1)) > 100, 
        VALUE(Text_Field__c) > 100)

The regex says to look for at least 1, but not more than 3 digits: [0-9]{1,3}. Then a group of characters consisting of a period (which has to be double-escaped with backslashes, once for Salesforce and again for the regex engine) and 1 or 2 instances of a digit: (\\.[0-9]{1,2}). The ? means that whole group should appear once or not at all. And then %? matches a percentage mark once, or not at all. Then the IF block checks to make sure that the number part is <= 100.

Related Topic