Can't work out Regex expression

Sharid

Well-known Member
Joined
Apr 22, 2007
Messages
1,057
Office Version
  1. 2016
Platform
  1. Windows
I am pulling of some Phone numbers off a few sites. However the current Regex expression clips some of the numbers. Does anyone know how to write one for global numbers and not just UK

Current expressions
VBA Code:
"(?:\+1)?(?:\+[0-9])?\(?([0-9]{3})\)?[-. ]?([0-9]{3})[-. ]?([0-9]{3}?)"

Results
1596202479624.png

As you can see numbers in Green have some digits missing.
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

yky

Well-known Member
Joined
Jun 7, 2011
Messages
1,873
Office Version
  1. 2010
Platform
  1. Windows
Would you share the complete VBA code?
 

Sharid

Well-known Member
Joined
Apr 22, 2007
Messages
1,057
Office Version
  1. 2016
Platform
  1. Windows
This is the shortened version as the top half just opens the IE. I have been searching for a combination that could do global numbers and not just UK.

VBA Code:
With regxp
''' ########## Phone Numbers Pattern ###########
        .Pattern = "(?:\+1)?(?:\+[0-9])?\(?([0-9]{4})\)?[-. ]?([0-9]{4})[-. ]?([0-9]{3})"
       '.Pattern = "(?:\+1)?(?:\+[0-9])?\(?([0-9]{3})\)?[-. ]?([0-9]{3})[-. ]?([0-9]{3}?)" ' also this combination
        .Global = False
        .IgnoreCase = True
        Set phone_list = .Execute(html.body.innerHtml)
     
'''########## PHONE LIST ############# ADD TO SHEET
    If phone_list(0) Is Nothing Then
        On Error Resume Next 'OTHERWISE I GET ERROR MESSAGE AND CODE CRASHES
            Sheet1.Cells(Sheet1.Cells(Sheet1.Rows.Count, "B").End(xlUp).Row + 1, "B").Value = "-"
    Else
        On Error Resume Next 'OTHERWISE I GET ERROR MESSAGE AND CODE CRASHES
            Sheet1.Cells(Sheet1.Cells(Sheet1.Rows.Count, "B").End(xlUp).Row + 1, "B").Value = phone_list(0)
    End If
End With


This is a good link on Stack Over Flow HOWEVER it does not state for what Language the REGXP is for, and this will differ for each language. The Stack Over Flow link may be for JAVA not 100% sure.

Thanks for having a look
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,497
maybe (?:\+1)?(?:\+[0-9])?\(?([0-9]{4})\)?[-. ]?([0-9]{4})[-. ]?([0-9]{3}?)

resregex.png
 

Sharid

Well-known Member
Joined
Apr 22, 2007
Messages
1,057
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

sandy666

Its an improvement, can't beleive it was just a question mark that i missed. Results are now coming off like this, still a few weird numbers which i have show in BLUE.
1596367620759.png

Will this work for global number or just UK?
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

@Sharid
- this is an Excel and VBA site
- we are not experts in every country's phone numbers
- if you want help in creating the REGEX expression that will achieve what you want, please list EVERY possible phone number pattern that must be recognised as a phone number
 

Sharid

Well-known Member
Joined
Apr 22, 2007
Messages
1,057
Office Version
  1. 2016
Platform
  1. Windows
It ok, I have done a work around, I have now changed the code to
VBA Code:
''' ########## Phone Numbers Pattern ###########
        .Pattern = ThisWorkbook.Sheets("Sheet1").Range("D1")
        .Global = False
        .IgnoreCase = True
        Set phone_list3 = .Execute(html.body.innerHtml)

This way I can keep changing the Regxp Pattern in Sheet1 Range D1, to pull off phone number types

Heres one for Skype
VBA Code:
(?<=skype:)[a-zA-Z][a-zA-Z|0-9|,|\-|_|\.]+

If anyone knows of a good website to build Regxp please let me know , or one that has a list of regxp per country.

Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,133,424
Messages
5,658,721
Members
418,466
Latest member
lisamck

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