0

Please advise a validation rule to allow me to enter 15 digits only.

If the number of entered digits is higher or less than 15 the validation rule should display an error message.

If there are spaces, special characters and letters entered in the field the validation rule should display an error message.

The validation should allow only 15 digit numbers to be entered into the field.

So far I used the following:

OR(

AND( LEN(custom_text_field__c) = 15, NOT(REGEX(custom_text_field__c,"^[0-9]+$")) ),

AND( LEN(custom_text_field__c) <> 15, REGEX(custom_text_field__c,"^[0-9]+$") )

)

However it is not working when there is a space between the digits. I'm still able to save the record without being stopped by the validation rule. Even with the following: "123 4567 89012345 1234 5678 9012345" the validation rule does not display and error message. Please advise how to include the spaces in the regex so that the validation rule to be triggered every time there is a space amidst the digits.

2

The logic is over-complicated; the second regex returns false when there are only numbers, but you actually wanted it to return true regardless. You can simplify this formula to:

OR( LEN(custom_text_field__c) <> 15, NOT(REGEX(custom_text_field__c,"^[0-9]+$")))

If you want to allow blank values, you need to add that in, too:

AND(NOT(ISBLANK(custom_text_field__c)),
OR( LEN(custom_text_field__c) <> 15, NOT(REGEX(custom_text_field__c,"^[0-9]+$"))))
  • @sdfcfox, it is working like a charm. Thank you so much. Because I need exactly the same rule for a second_custom_text_field and because I do not want to create an additional validation rule how can I include the two custom fields into the validation rule? In other words I need two custom fields included in one validation rule. Please advise – lemdev Jul 18 '18 at 15:23
  • @lemdev My general rule of advice is do not do that. The reason why is that you can only attach a validation error to either one field or the top of the page. Having two rules allows the user to instantly identify which field is in error by way of a red box and an error message. You should always prefer to use this method when validating a field, and that means having one validation rule per field to validate. – sfdcfox Jul 18 '18 at 15:26
1

My first thought is that you can accomplish most of that without a validation rule. Set the field type to number to exclude all non-number characters and set the field length to 15 to stop users from entering more than 15 characters.

After that the validation rule only needs to check for fewer than 15 characters.

LEN(CustomNumberField__c) != 15
  • 1
    That would introduce currency formatting, though, which might not be the intent here. For example, the field would be rendered as 123,456,789,012,345 for users that use a comma/decimal notation, 123 456 789 012 345 for users that use space/comma notation, etc. – sfdcfox Jul 18 '18 at 15:15
  • That's fair, not much difference from a programming perspective but definitely could render something that end users might not like. You could theoretically fix that via a second formula field using TEXT(firstfield) for rendering. At that point the regex is probably the better option though. – gNerb Jul 18 '18 at 15:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.