Phone Number Verifier

Question

This one verifies that a phone number is in the proper format: xxx-xxx-xxxx
&nbsp;
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)) &amp;&amp; LEN(__First3) = 3 &amp;&amp; NOT(CONTAINSSTRING(__First3,"-")) &amp;&amp; 
        NOT(ISERROR(__Second3+0)) &amp;&amp; LEN(__Second3)=3 &amp;&amp; NOT(CONTAINSSTRING(__Second3,"-")) &amp;&amp; 
        NOT(ISERROR(__Third4+0)) &amp;&amp; LEN(__Third4) = 4 &amp;&amp; NOT(CONTAINSSTRING(__Third4,"-")) &amp;&amp; 
         __FirstSep = __Sep &amp;&amp; 
        __SecondSep = __Sep &amp;&amp;
        LEN(__PhoneNumber) = __ValidLen,
        TRUE(),
        FALSE()
    )
Fixed the above to include overall length validation per&nbsp;AllisonKennedy&nbsp;. 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)) &amp;&amp; LEN(__CountryCode) = 1 &amp;&amp; NOT(CONTAINSSTRING(__CountryCode,"-")) &amp;&amp;     
        NOT(ISERROR(__First3+0)) &amp;&amp; LEN(__First3) = 3 &amp;&amp; NOT(CONTAINSSTRING(__First3,"-")) &amp;&amp; 
        NOT(ISERROR(__Second3+0)) &amp;&amp; LEN(__Second3)=3 &amp;&amp; NOT(CONTAINSSTRING(__Second3,"-")) &amp;&amp; 
        NOT(ISERROR(__Third4+0)) &amp;&amp; LEN(__Third4) = 4 &amp;&amp; NOT(CONTAINSSTRING(__Third4,"-")) &amp;&amp; 
         __FirstSep = __Sep &amp;&amp; __SecondSep = __Sep &amp;&amp; __CountrySep = __Sep &amp;&amp;
        LEN(__PhoneNumber) = __ValidLen,
        TRUE(),
        FALSE()
    )
&nbsp;
&nbsp;
eyJrIjoiZTdhMjc5NmUtOTU1NC00YjYxLWFhZGMtZGQ2MTY4MTQ4MjJkIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9

allisonkennedy · Answer

Pretty cool Greg_Deckler&nbsp;. 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?
&nbsp;
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 · Answer

AllisonKennedy&nbsp;- 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 · Answer

Greg_Deckler&nbsp; You're like me, always over thinking and over engineering things. 🙂&nbsp; Now you've got me too interested in this one, lol.&nbsp;
&nbsp;
OK, so your LEN check is required if they for example type:&nbsp;
800-588-230
and miss the last digit (but everything else passes the check), so perhaps it is needed after all.&nbsp;
&nbsp;
I still have one scenario that needs fixed in your formula, what if they type:&nbsp;
800-588-23001
That's not a valid phone number, but passes all your checks.&nbsp;
&nbsp;
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.&nbsp;
&nbsp;
&nbsp;

greg_deckler · Answer

AllisonKennedy&nbsp;- 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 · Answer

Thanks&nbsp;Greg_Deckler&nbsp;, it looks awesome now.&nbsp;
&nbsp;
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:&nbsp;
021 123 4567
027 1234 5678
0204 123 1234
021 123 45678
&nbsp;
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. 🙂&nbsp;