Regular Expression Pattern for five consecutive numerics only

user_10311743 0
Viewed 1279

I need a pattern to extract five consecutive numerics only.

ExampleA: 123456789 dsadss12345@3_- 1d22d333

ExampleB: 12345_ 2d2d2 aaa2222a

ExampleC: 2d2jj ddd_@12345

Wanted output: 12345

or

(not number: "espace"; "letter"; "symbols:,-& etc")(5 consecutive numbers)(not number: "espace"; "letter"; "symbols:,-& etc")

or

(5 consecutive numbers)(not number: "espace"; "letter"; "symbols:,-& etc")

or

(not number: "espace"; "letter"; "symbols:,-& etc")(5 consecutive numbers)

Tried these:

  (1)strPattern = "(((\s|\D|\W)(\d{5})(\s|\D|\W))|((\d{5})(\s|\D|\W))|((\s|\D|\W)(\d{5})))"
  (2)strPattern = "\d{5}"
  (3)strPattern = "(\s|\D|\W)(\d{5})(\s|\D|\W)"
  (4)strPattern = "\D(\d{5})\D"

(3) won't work with ExampleB

(2) won't work with ExampleA

(1) is the best I could get but it won't work in few cells.

vba excel regex
3 Answers

You may use

(?:^|\D)(\d{5})(?!\d)

See the regex demo.

The (?:^|\D) will match either the start of string or any char other than a digit, (\d{5}) will capture 5 digits into Group 1 and (?!\d) will make sure the next char is not a digit.

Sample code:

Dim re, targetString, colMatch, objMatch
Set re = New regexp
With re
 .pattern = "(?:^|\D)(\d{5})(?!\d)"
 .Global = True              ' Same as /g at the online tester
End With
targetString = "123456789 dsadss12345@3_- 1d22d333"
Set colMatch = re.Execute(targetString)
For Each objMatch In colMatch
  Debug.Print objMatch.SubMatches.Item(0)
Next
user_38329700

RegEx Cheat Sheet

Not really an answer on it's own but I think this excellent summary is worth it (and easier share here!)

Click images to view full-size.

user_81127760

This passed all of the tests outlined in OP:

((?<!\d)\d{5}(?!\d))

Full match will only be 5 consecutive digits surrounded by anything except for a digit

PROOF IS IN THE PUDDING:

https://regex101.com/r/Oz2aeR/1

user_30885100
Related