Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Rakshith RamachandraRakshith Ramachandra 

Formula to count the number of digits in a string

I'm trying to create a validation rule for Phone field in Salesforce. It accepts any number of digits. I want to limit it between 9-13. So I've to write up a validaition that does that. Remember I'm trying to count the number of  'DIGITS' only.
So I can't use OR(LEN(Phone) < 9, LEN(Phone) > 13). LEN counts the spaces, brackets etc.

For eg: (832) 999-9999 has 10 digits but 14 characters. LEN returns 14 not 10
Best Answer chosen by Rakshith Ramachandra
Rakshith RamachandraRakshith Ramachandra
I just figured out a temporary solution
OR( 
LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Phone," ",""),"-",""),"(",""),")","")) < 9, 
LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Phone," ",""),"-",""),"(",""),")","")) > 13)

If someone knows a better solution. Please go ahead

All Answers

Rakshith RamachandraRakshith Ramachandra
I just figured out a temporary solution
OR( 
LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Phone," ",""),"-",""),"(",""),")","")) < 9, 
LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Phone," ",""),"-",""),"(",""),")","")) > 13)

If someone knows a better solution. Please go ahead
This was selected as the best answer
Parker EdelmannParker Edelmann
Another solution is this: 
IF( LEN(Phone)=14 && CONTAINS(Phone,"-") /* assuming this is always a formatted 10 digit phone # */ 
     ,false
     ,!AND(LEN(Phone)>9, LEN(Phone)<13))
The first criteria is supposed to tell if the phone # is 10 digits and formatted. This is obviously a number that we want to be saved, so the 'then' criteria tells the rule not to fire. The 'else' criteria decides if it is within the acceptable range and tells the rule to act accordingly. Unfortunately this solution requires that phone numbers longer than 10 digits in length be unformatted as it works off of the number of characters in the latter part. It does have it's faults, but I hope it helps you.