Non Standard Phone List

smalik

Board Regular
Joined
Oct 26, 2006
Messages
134
Office Version
  1. 365
Platform
  1. Windows
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

1610148659143.png

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.
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
2,012
Office Version
  1. 2016
Platform
  1. Windows
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
DEFGH
1PhoneTempAreaNumberExtension
2(800) 555- 1234 80055512348005551234 
31 (800) 555-123480055512348005551234 
4(800) 555-1234 x33800555123433800555123433
5(800) 555-1234 (ext. 44)800555123444800555123444
6800-555-123480055512348005551234 
7(800) 555-1234 x 48005551234480055512344
8(800) 555-1234 x80055512348005551234 
9(800) 555-1234 ext80055512348005551234 
10555-1234 ext 87655512348765551234876 
111-800-123-4567 ext. 22580012345672258001234567225
12(800) 555-123480055512348005551234 
Sheet1
Cell Formulas
RangeFormula
E2:E12E2=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:F12F2=LEFT(E2,3)
G2:G12G2=MID(E2,4,7)
H2:H12H2=MID(E2,11,99)
 
Solution

smalik

Board Regular
Joined
Oct 26, 2006
Messages
134
Office Version
  1. 365
Platform
  1. Windows
Thank You Toadstool. You are a life saver.... This works. I can look for the one offs and fix them manually.
 

Dossfm0q

Banned User
Joined
Mar 9, 2009
Messages
570
Office Version
  1. 2019
Platform
  1. Windows
Option
Book1
DEFG
1PhoneAreaNumberExtension
2(800) 555- 1234 8005551234 
31 (800) 555-12348005551234 
4(800) 555-1234 x33800555123433
5(800) 555-1234 (ext. 44)800555123444
6800-555-12348005551234 
7(800) 555-1234 x 480055512344
8(800) 555-1234 x8005551234 
9(800) 555-1234 ext8005551234 
10555-1234 ext 8765551234876 
111-800-123-4567 ext. 2258001234567225
12(800) 555-12348005551234 
Sheet1
Cell Formulas
RangeFormula
E2:E12E2=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:F12F2=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:G12G2=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

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,497
just for fun...
PhoneAreaNumberExt
(800) 555- 1234 8005551234
1 (800) 555-12348005551234
(800) 555-1234 x33800555123433
(800) 555-1234 (ext. 44)800555123444
800-555-12348005551234
(800) 555-1234 x 480055512344
(800) 555-1234 x8005551234
(800) 555-1234 ext8005551234
555-1234 ext 8765551234876
1-800-123-4567 ext. 2258001234567225
(800) 555-12348005551234
181435194388143519438
148452110284845211028
148447320384844732038
148452110464845211046
148452110904845211090
181435193768143519376
181435194408143519440
181435193938143519393
161024489746102448974
148429184924842918492
148426346384842634638
148452110344845211034
+148426348234842634823
+148452196454845219645
+148429184924842918492
+148426348194842634819
+148429183164842918316
+148426348074842634807
+148426347624842634762
+148426346544842634654
+148426346934842634693
+148426346924842634692
+148426347914842634791
+148426346984842634698
+158543709795854370979
+158520953765852095376
+158520953495852095349
+158543709655854370965
+158543800295854380029
+158520953465852095346
+158543800365854380036
+158543801425854380142
+158520953685852095368
+158543802665854380266
+158520953705852095370
+158543801245854380124
+158543709725854370972
+158521358375852135837
+158520953525852095352
+158543801035854380103
+158543709625854370962
+158543801425854380142
+158520953705852095370
+158520953765852095376
+158521358395852135839
+158543802375854380237
+158543801695854380169
+158543801345854380134
+161024581486102458148
+161024580746102458074
+161024583266102458326
+161024580696102458069
+161023472886102347288
+161024583536102458353
+161024581356102458135
+161024581586102458158
+161024580766102458076
+161024489366102448936
+161024489216102448921
+161024581426102458142
+181435194668143519466
+181435195538143519553
+181430080708143008070
+181435194988143519498
+181435195048143519504
+181430080508143008050
+181435195028143519502
+181430080158143008015
+181430080198143008019
+181430080138143008013
+181430080248143008024
+181435193858143519385
+181435195408143519540
+181435194108143519410
+181435194518143519451
+181435193708143519370
+161024489446102448944
+181430089038143008903
+181435195308143519530
+161024580086102458008
+158543800225854380022
+181435195608143519560
+181435195558143519555
+158543800905854380090

Power Query:
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
 

Forum statistics

Threads
1,144,339
Messages
5,723,801
Members
422,518
Latest member
quack_quack

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top