Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
12-16-2020 11:24 AM - last edited 12-22-2020 08:11 AM
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
@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.
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. 🙂
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
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).
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
@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! 🙂
@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.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com