I need a data validation formula to say, if status is "Registered" and ID is 6 characters long, the last character of the ID must be a letter.

I have managed to get this far but come to a brick wall;

    ISPICKVAL( Referral_Status__c , 'Registered'), 
    LEN( Name ) = 6,

I believe you could use a Regular Expression in validation. See this answer.

The RegEx could enforce both length requirement as well as the letter requirement on character 6 (along with other things, like if you wanted to check for only alphanumerics in the first 5).

For example:

boolean passes = Pattern.matches('^[\w]{5}[a-zA-Z]$');

or perhaps if you want a more explicit regex:

boolean passes = Pattern.matches('^[a-zA-Z_0-9]{5}[a-zA-Z]$');


The Formula Operators and Functions documentation is a good resource here (albeit one that has a pretty abysmal UX).

There are several functions to get a substring, but probably the easiest one to use here given your description would be RIGHT(1) combined with REGEX() to test for an alphabetic character

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.