Phone Number Clean Up

[cmcreynolds]

Hello

I'm bringing in data from Salesforce using Power Query and I have to work with a phone number field that has the phone numbers in a various assortment of formats.

examples:

618-345-9876
(618) 899-0000
6181234567
+16185678901


Is there a way to clean up the field to get them all in the same format? I was thinking something along the lines of getting rid of any non-numeric characters, and then excluding any left-leading 1s. (Ultimately, I'm only needing the US Area code anyway, no other part of the number). But I don't know how to do that in PQ.

Any suggestions?

 

[Jon von der Heyden]

Hello

Perhaps a little crude but I did a quick and dirty replacement of "+1", "(", ")" and space character, leaving only numbers, as Text. Exhibit below assumes that the numbers are in a table called Table2 and that the phone numbers are in a column called 'Phone Number'.

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    MakeText = Table.TransformColumnTypes(Source,{{"Phone Number", type text}}),
    CleanTel = Table.AddColumn(MakeText, "Clean", each Text.Replace(Text.Replace(Text.Replace(Text.Replace(if Text.Start([Phone Number],2)="+1" then Text.Range([Phone Number],2) else [Phone Number],"(",""),")",""),"-","")," ",""))
in
    CleanTel

 

[Useful]

Hello,
here is another solution

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Chtyp = Table.TransformColumnTypes(Source,{{"Phone Number", type text}}),
    UResult = Table.AddColumn(Chtyp, "Custom", each Text.Combine(List.RemoveMatchingItems(Text.ToList([Phone Number]),{"(",")","-","+"," "})))
in
    UResult