Clean up List of Phone Numbers

[jleasure457]

Hello, I am working on a formula to clean up a list of phone numbers with very inconsistent formats to all include just the 3 digit area code and following 7 digits. I have a formula that removes all spaces and non-numeric characters, but I can't figure out what I need to add that would remove extensions at the end of the phone number or country codes at the beginning.. I am using: =TEXTJOIN("",TRUE, IFERROR(MID(A2, ROW(INDIRECT("1:100")),1)+0,"")) , and this is the result. If this could be better accomplished with a macro, that would be acceptable as well. Any help would be appreciated!

Original Phone Number	Number after Formula Applied
(999) 832-7870 (Mobile)	9998327870
999-995-1455	9999951455
1-999-525-7045	19995257045
(999) 864-3091 ext. 3137 (Direct)	99986430913137
(999) 526-3058 (Direct)	9995263058
(999) 549-8913 (HQ)	9995498913
+1 (999) 774-6041	19997746041
999-434-7736, ext 3158	99943477363158
(999) 422-8579 x9028	99942285799028
+44 (999) 774-6041	449997746041
+339997746041	339997746041

 

[alansidman]

@JEC
You are right. Misread the requirement. Will spend a bit of time reworking. Thanks for pointing that out.

 

[alansidman]

Corrected Code

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Custom1 = Table.TransformColumns(Source,
     {{"Original Phone Number", each Text.Replace(Text.Replace(_, "(", ""), ")", ""),"-",""}}),
    #"Added Custom" = Table.AddColumn(Custom1, "Custom", each List.Accumulate(

    List.Numbers(0, Table.RowCount(Corrections)),
    [Original Phone Number],
    (A,B) =>
    Text.Replace(A,Corrections[Find]{B}, Corrections[Replace]{B}))),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom"," ","",Replacer.ReplaceText,{"Custom"}),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Replaced Value", "Custom", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Custom.1", "Custom.2", "Custom.3"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Split Column by Character Transition",{"Custom.1"}),
    Trim= Test.End("Custom.1,10)
in
    Trim