Extracting Multiple Phone Numbers and putting into list format

MACHOMT · Jan 8, 2019

Hi guys,

I am trying to extract Phone Numbers ONLY from text that follows this common pattern within a single cell.

Phone Numbers
(510) 581-1111
Pacific Bell Telephone Company
(5/1/1995 - 1/8/2019) CH, Other/Historic
(510) 274-1111
Sbc Internet Services Inc
(3/1/2017 - 6/1/2018) Other/Historic
(415) 581-1111
Pacific Bell Telephone Company
(3/16/2016 - 3/16/2016) Other/Historic
(510) 581-2222
Mobile Phone Numbers
(510) 773-2222
Sprint Spectrum Lp
(7/19/2017 - 7/19/2017) Other/Historic

I want to have just the phone numbers, all with the format (111) 111-1111, extracted and placed into a single cell to look like this

(510) 581-1111
(510) 274-1111
(415) 581-1111
(510) 581-2222
(510) 773-2222

Is this possible? Any help is greatly appreciated. Thank you!

crazydragon84 · Jan 8, 2019

copy and paste into word or text file, copy that and paste it back into excel. does that paste each line into different line of excel (e.g. when i paste what you posted here and paste it to excel it pastes each line into a different cell). Then, you put a helper column 1, 2, 3, 1, 2, 3, 1,2,3, and auto filter on whatever number the phone number is in. copy that paste into word. Copy word, select a cell and paste the copied list of numbers into the formula bar. hope that works.

sandy666 · Jan 8, 2019

with PowerQuery

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Phone Numbers[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7](510) 581-1111[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Pacific Bell Telephone Company[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7](5/1/1995 - 1/8/2019) CH, Other/Historic[/td][/tr]

[tr=bgcolor:#FFFFFF][td](510) 274-1111[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Sbc Internet Services Inc[/td][/tr]

[tr=bgcolor:#FFFFFF][td](3/1/2017 - 6/1/2018) Other/Historic[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7](415) 581-1111[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Pacific Bell Telephone Company[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7](3/16/2016 - 3/16/2016) Other/Historic[/td][/tr]

[tr=bgcolor:#FFFFFF][td](510) 581-2222[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Mobile Phone Numbers[/td][/tr]

[tr=bgcolor:#FFFFFF][td](510) 773-2222[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Sprint Spectrum Lp[/td][/tr]

[tr=bgcolor:#FFFFFF][td](7/19/2017 - 7/19/2017) Other/Historic[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Custom[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA](510) 581-1111
(510) 274-1111
(415) 581-1111
(510) 581-2222
(510) 773-2222[/td][/tr]
[/table]

Code:

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table7"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Text.Trim([Phone Numbers],{"a".."z","A".."Z"," ","/",","})),
    #"Added Conditional Column" = Table.AddColumn(#"Added Custom", "Custom.1", each if Text.Contains([Custom], "/") then "" else [Custom]),
    #"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each ([Custom.1] <> "")),
    #"Added Custom1" = Table.AddColumn(#"Filtered Rows", "Custom.2", each "1"),
    #"Grouped Rows" = Table.Group(#"Added Custom1", {"Custom.2"}, {{"Count", each _, type table}}),
    #"Added Custom2" = Table.AddColumn(#"Grouped Rows", "Custom", each List.Distinct(Table.Column([Count],"Custom.1"))),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom2", {"Custom", each Text.Combine(List.Transform(_, Text.From), "#(lf)"), type text}),
    #"Removed Other Columns" = Table.SelectColumns(#"Extracted Values",{"Custom"})
in
    #"Removed Other Columns"[/SIZE]

MACHOMT · Jan 8, 2019

crazydragon84 said:
copy and paste into word or text file, copy that and paste it back into excel. does that paste each line into different line of excel (e.g. when i paste what you posted here and paste it to excel it pastes each line into a different cell). Then, you put a helper column 1, 2, 3, 1, 2, 3, 1,2,3, and auto filter on whatever number the phone number is in. copy that paste into word. Copy word, select a cell and paste the copied list of numbers into the formula bar. hope that works.

Thanks for the reply! The sheet I have has hundreds of columns with that data in one cell. Was hoping to find a formula that can extract it into another cell, without going through each one by one. Copying and pasting into word might take a bit of time.

Eric W · Jan 8, 2019

Welcome to the forum.

Your request is a bit much for standard Excel formulas. You can try PowerQuery like Sandy proposed, or you can use a UDF (User-Defined Function). If you'd like to try that, open a copy of your workbook. Press Alt-F11 to open the VBA editor. From the menu, click Insert > Module. Copy the following code into the sheet that opens:

Code:

Public Function GetNumbers(ByVal MyText As String, ByVal Delim As String) As String
Dim AllMatches As Object, Item As Object

    With CreateObject("VBScript.RegExp")
        .Pattern = "\(\d{3}\)\s\d{3}-\d{4}"
        .Global = True
        .MultiLine = True
        Set AllMatches = .Execute(MyText)
    End With
    For Each Item In AllMatches
        GetNumbers = GetNumbers & Delim & Item.Value
    Next Item
    GetNumbers = Mid(GetNumbers, Len(Delim) + 1)
    
End Function

Press Alt-Q to close the editor. Now you can use this formula:

=GetNumbers(A1,",")

where the comma is the delimiter between numbers. If you want the numbers stacked, then use:

=GetNumbers(A1,CHAR(10))

and make sure to put WordWrap on the cell. CHAR(10) is how to define the new line character. Note that this will only find numbers with the exact pattern you have shown. It may be possible to update it though to include other variations.

pgc01 · Jan 8, 2019

Hi

Similar to Eric's:

Code:

Function ExtractPhone(s As String)

With CreateObject("VBScript.RegExp")
    .Pattern = "(?:[\s\S]*?(\(\d{3}\) \d{3}-\d{4}))|[\s\S]*"
    .Global = True
    ExtractPhone = Mid(Replace(Application.Trim(.Replace(s, "$1 ")), "(", vbLf & "("), 2)
End With
End Function

In B1: =ExtractPhone(A1)

<table border="1" cellpadding="1" style="background:#FFF; border-collapse:collapse;border-width:2px;border-color:#CCCCCC;font-family:Arial,Arial; font-size:10pt" ><tr><th style="border-width:1px;border-color:#888888;background:#9CF " > </th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >A</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >B</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >C</th></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " >1</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">Phone Numbers (510) 581-1111 Pacific Bell Telephone Company (5/1/1995 - 1/8/2019) CH, Other/Historic (510) 274-1111 Sbc Internet Services Inc (3/1/2017 - 6/1/2018) Other/Historic (415) 581-1111 Pacific Bell Telephone Company (3/16/2016 - 3/16/2016) Other/Historic (510) 581-2222 Mobile Phone Numbers (510) 773-2222 Sprint Spectrum Lp (7/19/2017 - 7/19/2017) Other/Historic </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">(510) 581-1111 (510) 274-1111 (415) 581-1111 (510) 581-2222 (510) 773-2222</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " >2</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">Phone Numbers (510) 581-2222 Jane (123) 456-7890 Anne and Sarah (987) 654-3210 </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">(510) 581-2222 (123) 456-7890 (987) 654-3210</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " >3</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td colspan=4 style="background:#9CF; padding-left:1em" > [Book1]Sheet3</td></tr></table>

sandy666 · Jan 8, 2019

Update...

I overlooked that source is in a single cell, so here is:

Code:

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table15"]}[Content],
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"src", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "src"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Split Column by Delimiter", [PromoteAllScalars=true]),
    #"Added Custom" = Table.AddColumn(#"Promoted Headers", "Custom", each Text.Trim([Phone Numbers],{"a".."z","A".."Z"," ","/",","})),
    #"Added Conditional Column" = Table.AddColumn(#"Added Custom", "Custom.1", each if Text.Contains([Phone Numbers], "/") then "" else [Custom]),
    #"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each ([Custom.1] <> "")),
    #"Added Custom1" = Table.AddColumn(#"Filtered Rows", "Custom.2", each "1"),
    #"Grouped Rows" = Table.Group(#"Added Custom1", {"Custom.2"}, {{"Count", each _, type table}}),
    #"Added Custom2" = Table.AddColumn(#"Grouped Rows", "Phone Number", each List.Distinct(Table.Column([Count],"Custom.1"))),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom2",{"Phone Number"}),
    #"Extracted Values" = Table.TransformColumns(#"Removed Other Columns", {"Phone Number", each Text.Combine(List.Transform(_, Text.From), "#(lf)"), type text})
in
    #"Extracted Values"[/SIZE]

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]src[/td][td][/td][td=bgcolor:#70AD47]Phone Number[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Phone Numbers
(510) 581-1111
Pacific Bell Telephone Company
(5/1/1995 - 1/8/2019) CH, Other/Historic
(510) 274-1111
Sbc Internet Services Inc
(3/1/2017 - 6/1/2018) Other/Historic
(415) 581-1111
Pacific Bell Telephone Company
(3/16/2016 - 3/16/2016) Other/Historic
(510) 581-2222
Mobile Phone Numbers
(510) 773-2222
Sprint Spectrum Lp
(7/19/2017 - 7/19/2017) Other/Historic[/td][td][/td][td=bgcolor:#E2EFDA](510) 581-1111
(510) 274-1111
(415) 581-1111
(510) 581-2222
(510) 773-2222[/td][/tr]
[/table]

Rick Rothstein · Jan 8, 2019

For those who might be interested, you can write the UDF without resorting to Regular Expressions...

Code:

[table="width: 500"]
[tr]
	[td]Function PhoneNums(S As String) As String
  Dim X As Long, Lines() As String
  Lines = Split(S, vbLf)
  For X = 0 To UBound(Lines)
    If Lines(X) Like "(###) ###-####" Then PhoneNums = PhoneNums & vbLf & Lines(X)
  Next
  PhoneNums = Mid(PhoneNums, 2)
End Function[/td]
[/tr]
[/table]

Extracting Multiple Phone Numbers and putting into list format

MACHOMT

New Member

Excel Facts

crazydragon84

Board Regular

sandy666

Banned - Rules violations

MACHOMT

New Member

Eric W

MrExcel MVP

pgc01

MrExcel MVP

sandy666

Banned - Rules violations

Rick Rothstein

MrExcel MVP

Similar threads

Forum statistics

Share this page

Extracting Multiple Phone Numbers and putting into list format

New Member

Excel Facts

Board Regular

Banned - Rules violations

New Member

MrExcel MVP

MrExcel MVP

Banned - Rules violations

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock