Forum Discussion
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 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 |
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.
7 Replies
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.
Thanks for the formula, when i ran it the following error appeared
Please replace #"Changed Type" to the name of your previous step.
The error will get resolved.
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,
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
= 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")
