UDF to extract phone numbers from cell content

[satish78]

Hi Friends.

I have list of customer information in excel cell ColumnA like list of 1000 customers.
I want to extract all phone numbers present in each cell data. But phone numbers formats are different in each cell like

201.337.3900 or 213373900 or 1213373900 or 1(201 337-3900 or (201) 337.3900 or 201 337-3900 or 201 337-3900 or 201 337 3900 or 201-337-3900 or 201.337.3900 or 1201-337-3900 etc.

I think this is only possible with UDF.
I did search on posts related to udf and vba but not exact solution I found.

Satish

 

[wideboydixon]

Book1
	A	B
1	Phone: 201.337.3900	(201) 337-3900
2	Phone: 213373900
3	Phone: 1213373900	(121) 337-3900
4	Phone: 1(201 337-3900	(201) 337-3900
5	Phone: (201) 337.3900	(201) 337-3900
6	Phone: 201 337-3900	(201) 337-3900
7	Phone: 201 337-3900	(201) 337-3900
8	Phone: 201 337 3900	(201) 337-3900
9	Phone: 201-337-3900	(201) 337-3900
10	Phone: 201.337.3900	(201) 337-3900
11	Phone: 1201-337-3900	(201) 337-3900
Sheet1

Cell Formulas
Range	Formula
B1	=GetPhoneNumberFromText(A1)

Private regex As Object
Public Function GetPhoneNumberFromText(inputString As String) As String

Dim matches As Object

' Set default value - blank in this case but could be "Not Found" or similar
GetPhoneNumberFromText = ""

' Set up the regular expression first time around
If regex Is Nothing Then
    Set regex = CreateObject("VBScript.RegExp")
    regex.Pattern = "\(?([0-9]{3})\)?[-. ]?([0-9]{3})[-. ]?([0-9]{4})"
    regex.Global = True
End If

' Get the matches
Set matches = regex.Execute(inputString)

' Quit if we can't find a number
If matches.Count = 0 Then Exit Function

' Just process the first match
GetPhoneNumberFromText = regex.Replace(matches(0).Value, "($1) $2-$3")

End Function

It doesn't like your second and third examples though ...

WBD

 

[satish78]

some phone numbers are also like this +14015232592. in this udf is removing last digit 2 and result is (140) 152-3259.

	A	B
1	Phone: 201.337.3900	(201) 337-3900
2	Phone: 213373900
3	Phone: 1213373900	(121) 337-3900
4	Phone: 1(201 337-3900	(201) 337-3900
5	Phone: (201) 337.3900	(201) 337-3900
6	Phone: 201 337-3900	(201) 337-3900
7	Phone: 201 337-3900	(201) 337-3900
8	Phone: 201 337 3900	(201) 337-3900
9	Phone: 201-337-3900	(201) 337-3900
10	Phone: 201.337.3900	(201) 337-3900
11	Phone: 1201-337-3900	(201) 337-3900

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula B1 =GetPhoneNumberFromText(A1) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Private regex As Object
Public Function GetPhoneNumberFromText(inputString As String) As String

Dim matches As Object

' Set default value - blank in this case but could be "Not Found" or similar
GetPhoneNumberFromText = ""

' Set up the regular expression first time around
If regex Is Nothing Then
    Set regex = CreateObject("VBScript.RegExp")
    regex.Pattern = "\(?([0-9]{3})\)?[-. ]?([0-9]{3})[-. ]?([0-9]{4})"
    regex.Global = True
End If

' Get the matches
Set matches = regex.Execute(inputString)

' Quit if we can't find a number
If matches.Count = 0 Then Exit Function

' Just process the first match
GetPhoneNumberFromText = regex.Replace(matches(0).Value, "($1) $2-$3")

End Function

It doesn't like your second and third examples though ...

WBD

 

[wideboydixon]

Try changing the pattern to this:

    regex.Pattern = "(?:\+1)?(?:\+[0-9])?\(?([0-9]{3})\)?[-. ]?([0-9]{3})[-. ]?([0-9]{4})"

Note that you may need to press Ctrl+G and force the regex object to reset like this:

Set regex = Nothing

WBD

 

[satish78]

Its working. Thanks for your help and saving my time.

 

[satish78]

I do have another similar one but with vlookup its taking hours of time.

just like above

I have content in cell from which i need to extract state abbreviation or state name, if possible city as well.
I need udf to extract state name or abbreviation in Sheet1 columnB

example:

sheet1A Content contains state abbreviation or state name after name of person.
In sheet2 ColumnA we have state names and abbreviations in columnb.
here is sample file https://docs.google.com/spreadsheets/d/1Qw0CaBUP1Qo0uaXRO-jlKdT6wJu5xfhrs84QGBzFlLs/edit?usp=sharing

Try changing the pattern to this:

    regex.Pattern = "(?:\+1)?(?:\+[0-9])?\(?([0-9]{3})\)?[-. ]?([0-9]{3})[-. ]?([0-9]{4})"

Note that you may need to press Ctrl+G and force the regex object to reset like this:

Set regex = Nothing

WBD

 

[satish78]

Is there a better way to extract country codes as well or instead of extract below format, I need the script to extract all phone numbers, either 10, 11,12,13,14,15,16 etc digits including space or without space after country code.

	A	B
1	Phone: 201.337.3900	(201) 337-3900
2	Phone: 213373900
3	Phone: 1213373900	(121) 337-3900
4	Phone: 1(201 337-3900	(201) 337-3900
5	Phone: (201) 337.3900	(201) 337-3900
6	Phone: 201 337-3900	(201) 337-3900
7	Phone: 201 337-3900	(201) 337-3900
8	Phone: 201 337 3900	(201) 337-3900
9	Phone: 201-337-3900	(201) 337-3900
10	Phone: 201.337.3900	(201) 337-3900
11	Phone: 1201-337-3900	(201) 337-3900

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula B1 =GetPhoneNumberFromText(A1) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Private regex As Object
Public Function GetPhoneNumberFromText(inputString As String) As String

Dim matches As Object

' Set default value - blank in this case but could be "Not Found" or similar
GetPhoneNumberFromText = ""

' Set up the regular expression first time around
If regex Is Nothing Then
    Set regex = CreateObject("VBScript.RegExp")
    regex.Pattern = "\(?([0-9]{3})\)?[-. ]?([0-9]{3})[-. ]?([0-9]{4})"
    regex.Global = True
End If

' Get the matches
Set matches = regex.Execute(inputString)

' Quit if we can't find a number
If matches.Count = 0 Then Exit Function

' Just process the first match
GetPhoneNumberFromText = regex.Replace(matches(0).Value, "($1) $2-$3")

End Function

It doesn't like your second and third examples though ...

WBD

 

[satish78]

I am concentrating USA phone numbers only. So, people use 1 or +1 or 1(area code) or +1(area code) or area code only to identify there phone/mobile numbers.
Hope you understand.

it is far better

Is there a better way to extract country codes as well or instead of extract below format, I need the script to extract all phone numbers, either 10, 11,12,13,14,15,16 etc digits including space or without space after country code.