Forum Discussion

Mr_Triongl's avatar
Mr_Triongl
Frequent Visitor
3 years ago
Solved

How to validate phone numbers

Hi,   I have a list of phone numbers in my data and want to validate that the numbers are in the correct format - Starting with 01/+44 and 11 digits in total. Anything with less digits or greater t...
Need Help
  • adudani's avatar
    adudani
    3 years ago

    hi Mr_Triongl ,

     

    create a new step ( press fx)

     

    paste the following in the formula bar:

     

    = Table.AddColumn(#"Changed Type", "Custom", each if Text.StartsWith([Phone Number],"01",Comparer.OrdinalIgnoreCase) or Text.StartsWith([Phone Number],"+44",Comparer.OrdinalIgnoreCase) and Text.Length([Phone Number])= 11 then "Valid" else "Invalid")

     

    Appreciate a thubs up if this is helpful.

     

    Please accept as the solution if it resolves the question.

Mr_Triongl's avatar
Mr_Triongl
Frequent Visitor
3 years ago

I've noticed that this works for most telephone numbers, but it also sets numbers with 12 digits and any additional text as valid as well instead of setting them to invalid.

 

12 digit example - 012345678900  

Text in cell - 01234567890 please call after 5pm 

 

I'm trying to get these set to Invalid as well as they aren't correct because of the additional digit in one and that someone has added a note to the second one.

Thanks,

adudani's avatar
adudani
Memorable Member
3 years ago

Mr_Triongl  does splitting this column by delimeter ( space) take all notes to another column? 

If this is not the case will revamp the formula, could you please provide more sample inputs for those that are valid that should be invalid in a table? Thanks