1

I need to write a validation rule for phone numbers to only start with 0 and be 11 digits long, no more, no less.

This is what I have so far below: At the moment, it is accepting 10 digits and I am able to save the record with only 10 digits. I need it to be 11 digits, no more, no less but I'm finding it difficult to correct it. Would really appreciate the help.

Many thanks

AND(
    OR(
        ISBLANK(Phone),
        NOT(REGEX(Phone, "^(0)[1-9]{1}([0-9]{8,9})$"))
    ),
    OR (
        Profile.Name = "Sales Agent",
        Profile.Name = "Field Sales Agent",
        Profile.Name = "Field Service Agent",
        Profile.Name = "Lead Gen Agent",
        Profile.Name = "TEL Sales Agent",
        Profile.Name = "THS Sales Agent"
    )
)
2
  • What if you need to enter an extension in the phone number like "ext 123"? Commented Jul 26, 2021 at 17:12
  • No need for extension just straight up dial. Ext has a separate field @David Cheng Commented Jul 26, 2021 at 17:15

2 Answers 2

2

Just go low tech.

Add this to your OR statement: NOT(LEN(LastName) = 12)

Ie:

OR(     
        NOT(LEN(Phone) = 11),
        ISBLANK(Phone),
        NOT(REGEX(Phone, "^(0)[1-9]{1}([0-9]{8,9})$"))
    ),

This has the benefit of be a whole lot easier to read than the regex, which you can still use to validate other stuff.

3
  • 1
    probably can remove the regex completely and use ISNUMBER(Phone) along with BEGINS(Phone, '0') Commented Jul 26, 2021 at 19:45
  • Yep, definitely. Regex in validation rules is just horrible. No debugging, strange syntax. Yeuch. Commented Jul 26, 2021 at 19:50
  • 1
    Thanks @CasparHarmer! Very useful Commented Jul 26, 2021 at 21:17
6

The bit you're concerned with here is NOT(REGEX(Phone, "^(0)[1-9]{1}([0-9]{8,9})$"))

It's pretty much always a good idea to understand the tools that you're using (which is why I personally avoid using and giving copy/paste-able answers), so let's break down this regex. Understanding what the regex means should make it easier for you to identify and change the correct part of it.

  • ^ = Search for the following starting at only the start of the string
  • (0) = look for a 0 (enclosing it in parenthesis makes it a capturing group in most regex tools
  • [1-9]{1} = look for exactly 1 (that's the {1} part) symbol that's between '1' and '9' (i.e. every digit except for 0)
  • ([0-9]{8,9}) = another capturing group. This one looks for digits 0-9, and will match between 8 and 9 repeated digits
  • $ = the preceding expression must occur at the end of the string

^<expression>$ is then "this expression must match the entire string"
{x,y} means "repeated between x times at least, and no more than y times"

counting up the characters you're expecting, you have

  • Leading 0
  • A single digit (1-9)
  • 8 or 9 digits (0-9)

2 + 8 = 10, which is why your validation rule is allowing a 10-digit phone number

If you want precisely 11 digits, then you need to change that {8,9} so that it only accepts 9 digits for that part of your expression.

{x} means "only accept exactly this many characters", so {9} there should work.

2
  • There are other ways to enforce a length of 11, I just chose the easiest one. You could, for example, require that the 1-9 portion be precisely 3 digits long, and then reduce the 0-9 portion to be precisely 7. I guess the main point here is that if you want exact string length in a regex, you can't use a range like {8,9}
    – Derek F
    Commented Jul 26, 2021 at 17:34
  • Thanks @Derek F. Very useful! Commented Jul 26, 2021 at 21:13

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .