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

 

[JEC]

**snipp**

 

[JEC]

what is your expected output?

 

[jleasure457]

what is your expected output?

Hi @JEC . Thanks for the reply. I would like it to only include the 3 digit area code and 7 digits following. I've added a column below and indicated the cells in bold which I'm not receiving the expected output.

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

 

[JEC]

Here a solution with a UDF. Place this behind a new module

Function jec(c As String) As String
  With CreateObject("vbscript.regexp")
    .Pattern = "(?:.*?)(999)(\))?( |-|)(.{7,8})(.*)"
    jec = Replace(.Replace(c, "$1$4"), "-", "")
 End With
End Function

Then you can use it like below

Book1
	A	B
1	Original Phone Number	UDF
2	(999) 832-7870 (Mobile)	9998327870
3	999-995-1455	9999951455
4	1-999-525-7045	9995257045
5	(999) 864-3091 ext. 3137 (Direct)	9998643091
6	(999) 526-3058 (Direct)	9995263058
7	(999) 549-8913 (HQ)	9995498913
8	+1 (999) 774-6041	9997746041
9	999-434-7736, ext 3158	9994347736
10	(999) 422-8579 x9028	9994228579
11	+44 (999) 774-6041	9997746041
12	+339997746041	9997746041
Sheet2

Cell Formulas
Range	Formula
B2:B12	B2	=jec(A2)

Or with an Excel formula

=LET(t,MID(A3,SEARCH("999",A2),13),TEXTJOIN("",,IFERROR(--MID(t,SEQUENCE(LEN(t)),1),"")))

 

[jleasure457]

Thanks @JEC - the VBA script works well for the dataset provided, but I failed to clarify that my actual data set uses a variety of USA area codes. So, they will not consistently have utilize "999" as the first three numbers. Do you have any other suggestions?

 

[JEC]

How about this?

Function jec(c As String) As String
  With CreateObject("vbscript.regexp")
    .Pattern = "(?:.*?)(\+\d+)?(\d{3})(\))?( |-|)(.{7,8})(.*)"
    jec = Replace(.Replace(c, "$2$5"), "-", "")
 End With
End Function

 

[alansidman]

With Power Query

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"})
in
    #"Removed Other Columns"

Book2
	A	B	C
1	Original Phone Number		Custom.1
2	(999) 832-7870 (Mobile)		9998327870
3	999-995-1455		9999951455
4	1-999-525-7045		19995257045
5	(999) 864-3091 ext. 3137 (Direct)		9998643091
6	(999) 526-3058 (Direct)		9995263058
7	(999) 549-8913 (HQ)		9995498913
8	+1 (999) 774-6041		19997746041
9	999-434-7736, ext 3158		9994347736
10	(999) 422-8579 x9028		9994228579
11	+44 (999) 774-6041		449997746041
12	339997746041		339997746041
Sheet1

 

[JEC]

@Alan, check the expected output in 4

 

[MARK858]

@JEC I think you will find that you have tagged the wrong member