Non Standard Phone List

[smalik]

I have a list of 90,000+ US phones numbers in an Excel file. I need to parse them out into three columns

• Area Code
• 7 digit number with a "-" in between (123-4567)
• Extension

However, the data is not in a standard format. Even some of them have extra spaces at the end. (I could get rid of them using the Trim function). Here are the possible combinations I found:

A little explanation for the Excel experts who are not familiar with the the US Phone number format

• 1 is the country code that can be eliminated by search replace. It is not necessary for this exercise
• Area code is the first three digits either (800) or 800
• next 8 digits are the phone number including the hyphen
• The number after the x is the extension, if there is an extension

For confidentiality reasons, the phone numbers are modified. Area code (800) could be any number and so are the first three digits after area code (555)

I do not know VBA so I like to see if this can be done using a formula? I don't mind doing this in multiple steps if need be..

Any help is greatly appreciated.

Thanks.

 

[Toadstool]

Hi SMalik,

You could reformat into a work area then extract. The challenge is you'd need to add logic for every possible exception to highlight issues (e.g. here row 10 with the short number doesn't reformat properly).

SMalik.xlsx
	D	E	F	G	H
1	Phone	Temp	Area	Number	Extension
2	(800) 555- 1234	8005551234	800	5551234
3	1 (800) 555-1234	8005551234	800	5551234
4	(800) 555-1234 x33	800555123433	800	5551234	33
5	(800) 555-1234 (ext. 44)	800555123444	800	5551234	44
6	800-555-1234	8005551234	800	5551234
7	(800) 555-1234 x 4	80055512344	800	5551234	4
8	(800) 555-1234 x	8005551234	800	5551234
9	(800) 555-1234 ext	8005551234	800	5551234
10	555-1234 ext 876	5551234876	555	1234876
11	1-800-123-4567 ext. 225	8001234567225	800	1234567	225
12	(800) 555-1234	8005551234	800	5551234
Sheet1

Cell Formulas
Range	Formula
E2:E12	E2	=TRIM(IF(LEFT(D2,1)="1",MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D2,"(",""),")","")," ",""),"-",""),"ext",""),"x",""),".",""),2,99),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D2,"(",""),")","")," ",""),"-",""),"ext",""),"x",""),".","")))
F2:F12	F2	=LEFT(E2,3)
G2:G12	G2	=MID(E2,4,7)
H2:H12	H2	=MID(E2,11,99)

 

[smalik]

Thank You Toadstool. You are a life saver.... This works. I can look for the one offs and fix them manually.

 

[Dossfm0q]

Option

Book1
	D	E	F	G
1	Phone	Area	Number	Extension
2	(800) 555- 1234	800	5551234
3	1 (800) 555-1234	800	5551234
4	(800) 555-1234 x33	800	5551234	33
5	(800) 555-1234 (ext. 44)	800	5551234	44
6	800-555-1234	800	5551234
7	(800) 555-1234 x 4	800	5551234	4
8	(800) 555-1234 x	800	5551234
9	(800) 555-1234 ext	800	5551234
10	555-1234 ext 876	555	1234876
11	1-800-123-4567 ext. 225	800	1234567	225
12	(800) 555-1234	800	5551234
Sheet1

Cell Formulas
Range	Formula
E2:E12	E2	=MID(SUBSTITUTE(SUBSTITUTE(CONCAT(IF(ISNUMBER(NUMBERVALUE(MID($D2,ROW(INDIRECT("1:"&LEN($D2))),1))),MID($D2,ROW(INDIRECT("1:"&LEN($D2))),1),""))," ",""),1800,800),1,3)
F2:F12	F2	=MID(SUBSTITUTE(SUBSTITUTE(CONCAT(IF(ISNUMBER(NUMBERVALUE(MID($D2,ROW(INDIRECT("1:"&LEN($D2))),1))),MID($D2,ROW(INDIRECT("1:"&LEN($D2))),1),""))," ",""),1800,800),4,7)
G2:G12	G2	=MID(SUBSTITUTE(SUBSTITUTE(CONCAT(IF(ISNUMBER(NUMBERVALUE(MID($D2,ROW(INDIRECT("1:"&LEN($D2))),1))),MID($D2,ROW(INDIRECT("1:"&LEN($D2))),1),""))," ",""),1800,800),11,7)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[sandy666]

just for fun...

Phone		Area	Number	Ext
(800) 555- 1234		800	5551234
1 (800) 555-1234		800	5551234
(800) 555-1234 x33		800	5551234	33
(800) 555-1234 (ext. 44)		800	5551234	44
800-555-1234		800	5551234
(800) 555-1234 x 4		800	5551234	4
(800) 555-1234 x		800	5551234
(800) 555-1234 ext		800	5551234
555-1234 ext 876			5551234	876
1-800-123-4567 ext. 225		800	1234567	225
(800) 555-1234		800	5551234
18143519438		814	3519438
14845211028		484	5211028
14844732038		484	4732038
14845211046		484	5211046
14845211090		484	5211090
18143519376		814	3519376
18143519440		814	3519440
18143519393		814	3519393
16102448974		610	2448974
14842918492		484	2918492
14842634638		484	2634638
14845211034		484	5211034
+14842634823		484	2634823
+14845219645		484	5219645
+14842918492		484	2918492
+14842634819		484	2634819
+14842918316		484	2918316
+14842634807		484	2634807
+14842634762		484	2634762
+14842634654		484	2634654
+14842634693		484	2634693
+14842634692		484	2634692
+14842634791		484	2634791
+14842634698		484	2634698
+15854370979		585	4370979
+15852095376		585	2095376
+15852095349		585	2095349
+15854370965		585	4370965
+15854380029		585	4380029
+15852095346		585	2095346
+15854380036		585	4380036
+15854380142		585	4380142
+15852095368		585	2095368
+15854380266		585	4380266
+15852095370		585	2095370
+15854380124		585	4380124
+15854370972		585	4370972
+15852135837		585	2135837
+15852095352		585	2095352
+15854380103		585	4380103
+15854370962		585	4370962
+15854380142		585	4380142
+15852095370		585	2095370
+15852095376		585	2095376
+15852135839		585	2135839
+15854380237		585	4380237
+15854380169		585	4380169
+15854380134		585	4380134
+16102458148		610	2458148
+16102458074		610	2458074
+16102458326		610	2458326
+16102458069		610	2458069
+16102347288		610	2347288
+16102458353		610	2458353
+16102458135		610	2458135
+16102458158		610	2458158
+16102458076		610	2458076
+16102448936		610	2448936
+16102448921		610	2448921
+16102458142		610	2458142
+18143519466		814	3519466
+18143519553		814	3519553
+18143008070		814	3008070
+18143519498		814	3519498
+18143519504		814	3519504
+18143008050		814	3008050
+18143519502		814	3519502
+18143008015		814	3008015
+18143008019		814	3008019
+18143008013		814	3008013
+18143008024		814	3008024
+18143519385		814	3519385
+18143519540		814	3519540
+18143519410		814	3519410
+18143519451		814	3519451
+18143519370		814	3519370
+16102448944		610	2448944
+18143008903		814	3008903
+18143519530		814	3519530
+16102458008		610	2458008
+15854380022		585	4380022
+18143519560		814	3519560
+18143519555		814	3519555
+15854380090		585	4380090

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Text = Table.TransformColumnTypes(Source,{{"Phone", type text}}),
    TS = Table.AddColumn(Text, "TS", each Text.Select([Phone], {"0".."9","x"})),
    First = Table.AddColumn(TS, "First Characters", each Text.Start([TS], 1), type text),
    IF = Table.AddColumn(First, "Custom", each if [First Characters] = "1" then Text.Middle([TS], 1, 99) else [TS]),
    Split = Table.SplitColumn(IF, "Custom", Splitter.SplitTextByDelimiter("x", QuoteStyle.Csv), {"Custom.1", "Custom.2"}),
    Len = Table.AddColumn(Split, "Length", each Text.Length(Text.From([Custom.1], "en-GB")), Int64.Type),
    IF2 = Table.AddColumn(Len, "Custom", each if [Length] = 10 then Text.Middle([Custom.1], 0, 3) else null),
    IF3 = Table.AddColumn(IF2, "Custom.3", each if [Length] = 10 then Text.Middle([Custom.1], 3, 99) else [Custom.1]),
    TSC = Table.SelectColumns(IF3,{"Custom", "Custom.3", "Custom.2"}),
    Result = Table.RenameColumns(TSC,{{"Custom", "Area"}, {"Custom.3", "Number"}, {"Custom.2", "Ext"}})
in
    Result