Can't work out Regex expression

Sharid

Well-known Member
Joined
Apr 22, 2007
Messages
847
Office Version
2016
Platform
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.
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

yky

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

Sharid

Well-known Member
Joined
Apr 22, 2007
Messages
847
Office Version
2016
Platform
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

Well-known Member
Joined
Oct 24, 2015
Messages
5,778
maybe (?:\+1)?(?:\+[0-9])?\(?([0-9]{4})\)?[-. ]?([0-9]{4})[-. ]?([0-9]{3}?)

resregex.png
 

Sharid

Well-known Member
Joined
Apr 22, 2007
Messages
847
Office Version
2016
Platform
Windows
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,348
Office Version
365
Platform
Windows
@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
847
Office Version
2016
Platform
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,102,350
Messages
5,486,345
Members
407,541
Latest member
Emilybuhman

This Week's Hot Topics

Top