Forum Discussion
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...
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.
= 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")