Phone Number Verifier 12-22-2020 08:11 AM

[Greg_Deckler]

This one verifies that a phone number is in the proper format: xxx-xxx-xxxx

Phone Number Verifier = 
    VAR __ValidLen = 12
    VAR __Sep = "-"
    VAR __PhoneNumber = MAX([Phone#])
    VAR __First3 = LEFT(__PhoneNumber,3)
    VAR __FirstSep = MID(__PhoneNumber,4,1)
    VAR __Second3 = MID(__PhoneNumber,5,3)
    VAR __SecondSep = MID(__PhoneNumber,8,1)
    VAR __Third4 = RIGHT(__PhoneNumber,4)
RETURN
    SWITCH(TRUE(),
        NOT(ISERROR(__First3+0)) && LEN(__First3) = 3 && NOT(CONTAINSSTRING(__First3,"-")) && 
        NOT(ISERROR(__Second3+0)) && LEN(__Second3)=3 && NOT(CONTAINSSTRING(__Second3,"-")) && 
        NOT(ISERROR(__Third4+0)) && LEN(__Third4) = 4 && NOT(CONTAINSSTRING(__Third4,"-")) && 
         __FirstSep = __Sep && 
        __SecondSep = __Sep &&
        LEN(__PhoneNumber) = __ValidLen,
        TRUE(),
        FALSE()
    )

Fixed the above to include overall length validation per @AllisonKennedy . Also, based on her suggestion, here is an international version:

International Phone Number Verifier = 
    VAR __ValidLen = 14
    VAR __Sep = "-"
    VAR __PhoneNumber = MAX([Phone#])
    VAR __CountryCode = LEFT(__PhoneNumber,1)
    VAR __CountrySep = MID(__PhoneNumber,2,1)
    VAR __First3 = MID(__PhoneNumber,3,3)
    VAR __FirstSep = MID(__PhoneNumber,6,1)
    VAR __Second3 = MID(__PhoneNumber,7,3)
    VAR __SecondSep = MID(__PhoneNumber,10,1)
    VAR __Third4 = RIGHT(__PhoneNumber,4)
RETURN
    SWITCH(TRUE(),
        NOT(ISERROR(__CountryCode+0)) && LEN(__CountryCode) = 1 && NOT(CONTAINSSTRING(__CountryCode,"-")) &&     
        NOT(ISERROR(__First3+0)) && LEN(__First3) = 3 && NOT(CONTAINSSTRING(__First3,"-")) && 
        NOT(ISERROR(__Second3+0)) && LEN(__Second3)=3 && NOT(CONTAINSSTRING(__Second3,"-")) && 
        NOT(ISERROR(__Third4+0)) && LEN(__Third4) = 4 && NOT(CONTAINSSTRING(__Third4,"-")) && 
         __FirstSep = __Sep && __SecondSep = __Sep && __CountrySep = __Sep &&
        LEN(__PhoneNumber) = __ValidLen,
        TRUE(),
        FALSE()
    )

eyJrIjoiZTdhMjc5NmUtOTU1NC00YjYxLWFhZGMtZGQ2MTY4MTQ4MjJkIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9

[Greg_Deckler]

@AllisonKennedy - Love all of the suggestions and comments. I have updated this to include the fix for overall length and have included an International phone number verifier at least for United States and single digit country codes. Will have to think through being able to handle country codes of various lengths.

[AllisonKennedy]

Thanks @Greg_Deckler , it looks awesome now. 

Good luck with the international country codes. As I mentioned, we have no fixed length for phone numbers in in NZ, nor is there a set way to display them. Mobile numbers can be for example: 

021 123 4567

027 1234 5678

0204 123 1234

021 123 45678

Depends who you ask if they split the last digits equally, by twos, by three then the rest, etc. And the prefixes relate to the cell carrier, but some of them are 3 digits and some are 4 digits. Really confusing! But I always enter country code now for US numbers, so at least your check will help us internationals to correctly type a US phone number. 🙂 

[AllisonKennedy]

Pretty cool @Greg_Deckler . I can't believe this doesn't exist yet! A silly question; why do you need to check the len() of the parts? Didn't you do that in creating the variables? Also, what if they add an extra digit to the end?

Now if you could create a check for NZ phone numbers you would be a genius (they don't have consistent formatting or length).

[Greg_Deckler]

@AllisonKennedy - I guess my thinking was to be thorough. I think it comes into play if the numbers are short. So, for example you grab the last 4 digits but there are only 3 digits to grab. I don't know, like most things I create, they can almost always be optimized! 🙂

[AllisonKennedy]

@Greg_Deckler  You're like me, always over thinking and over engineering things. 🙂  Now you've got me too interested in this one, lol. 

OK, so your LEN check is required if they for example type: 

800-588-230

and miss the last digit (but everything else passes the check), so perhaps it is needed after all. 

I still have one scenario that needs fixed in your formula, what if they type: 

800-588-23001

That's not a valid phone number, but passes all your checks. 

Also, being a non-US resident now, I'd love to see it handle this format:

1-800-588-2300

as valid (or even tell me three cases: "Valid No Country Code", "Valid with Country Code", "Invalid") but now I'm just getting picky.