Can't work out Regex expression

Sharid

Well-known Member
Joined
Apr 22, 2007
Messages
1,064
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

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Would you share the complete VBA code?
 
Upvote 0
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
 
Upvote 0
maybe (?:\+1)?(?:\+[0-9])?\(?([0-9]{4})\)?[-. ]?([0-9]{4})[-. ]?([0-9]{3}?)

resregex.png
 
Upvote 0
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?
 
Upvote 0
@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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,993
Members
448,539
Latest member
alex78

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
Back
Top