How to validate phone numbers

[Mr_Triongl]

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 to show as Invalid. Also any text in the phone number to be invalid as well

Below is what I'm trying to accomplish:

Asset_ID	Phone Number	Validation
01	01234567890	Valid
02	+441234567890	Valid
03	050547415	Invalid
04	01234567891 please call after 5pm	Invalid
05	051234567890	Invalid
06	01324567890	Valid

[wdx223_Daniel]

= Table.AddColumn(Source,"Validation",each let a={"01","+44"},b=List.PositionOf({"01","+44"},[Phone Number],0,(x,y)=>Text.StartsWith(y,x)) in if b>=0 and Text.Remove(Text.Range([Phone Number],Text.Length(a{b})),{"0".."9"})="" and Text.Length([Phone Number])-Text.Length(a{b})=9 then "Valid" else "Invalid")

[adudani · Accepted solution]

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]

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]

@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 

[Mr_Triongl]

Thanks for the formula, when i ran it the following error appeared

[adudani]

@Mr_Triongl 

Please replace #"Changed Type" to the name of your previous step.

The error will get resolved. 

[Mr_Triongl]

Thanks for that @adudani that formula worked a treat.