+ Start a Discussion
Jeff_BrownJeff_Brown 

Phone Number Data Validation

Hello,

Im trying to create a data validation rule for the phone number field to only accept the following formats +99 9 9999 9999 or +99 9 999 9999.
I found and modified the below online which works well for the number part, however I cant figure out how to add the + into this validation rule...

AND( 
IF(ISBLANK(Phone) , false , NOT( REGEX( Phone,"[0-9]{2}[\\s][0-9]{1}[\\s][0-9]{3}[\\s][0-9]{4}"))), 
IF(ISBLANK(Phone) , false , NOT( REGEX( Phone,"[0-9]{2}[\\s][0-9]{1}[\\s][0-9]{4}[\\s][0-9]{4}"))) 
)

I also found the below which works on its own, but I can figure out how to join these two into one validation rule...
LEFT(Phone, 1) <> "+" 

Please help.
Thanks Jeff
Best Answer chosen by Jeff_Brown
Pritam ShekhawatPritam Shekhawat
Try this formula,
NOT( OR( 
OR( LEN( Phone ) = 0, 
REGEX(Phone, "[+]{1}[0-9]{2}[\\s][0-9]{1}[\\s][0-9]{3}[\\s][0-9]{4}") , 
REGEX(Phone, "[+]{1}[0-9]{2}[\\s][0-9]{1}[\\s][0-9]{4}[\\s][0-9]{4}"))))

 

All Answers

Pritam ShekhawatPritam Shekhawat
Try this formula,
NOT( OR( 
OR( LEN( Phone ) = 0, 
REGEX(Phone, "[+]{1}[0-9]{2}[\\s][0-9]{1}[\\s][0-9]{3}[\\s][0-9]{4}") , 
REGEX(Phone, "[+]{1}[0-9]{2}[\\s][0-9]{1}[\\s][0-9]{4}[\\s][0-9]{4}"))))

 
This was selected as the best answer
Jeff_BrownJeff_Brown
Hi Pritam,

Thank you so much, this worked perfectly. Really appreciate your help.

Thanks,
Jeff