VBA- Finding texts built from regex patterns
 

I need to recognise patterns in text starting with a specific variable text value and ending in 0-3 numeric values.




For example:




I have several strings of text that I am searching for at the start of a text string:




S

FDC

PP

Doctor




So I will want to loop through these (already in an array) and test whether a text string starts with these values and ends with 0-2 numeric digits:




i.e. Text string "S01" is true

"PQ" is false

"FDC99" is true

"Doctor" is true

"Dr01" is false




I have not worked much with Regex before so am struggling to workout how I recognise the pattern for each of the strings. Any ideas?




If I do find ,matches, I will then replace the text part with a different string




i.e. "S" is replaced by "Stamps "
"FDC" is replaced by "First Day Covers "


"PP" is replaced by "Presentation Packs "
"Doctor" is replaced by "Doctor Who "



The replacement texts are in the same array BinderNames(1 To BinderCount, 1 To 2) so the first and 2nd entries in each row are the search text and the replacement text.




Is there a simple way of using Regex to do this?

VBA- Finding texts built from regex patterns
 

Hi,

Am Sat, 4 Mar 2017 03:53:52 -0800 (PST) schrieb stainless:

I need to recognise patterns in text starting with a specific variable text value and ending in 0-3 numeric values.

For example:

I have several strings of text that I am searching for at the start of a text string:

S

FDC

PP

Doctor

try:

Sub Test()
Dim re As Object
Dim ptrn As String
Dim Matches
Dim rngC As Range
Dim varSearch As Variant
Dim i As Long

varSearch = Range("Binder")
Set re = CreateObject("vbscript.regexp")
'Modify the range with your strings
For Each rngC In Range("A1:A10")
For i = LBound(varSearch) To UBound(varSearch)
ptrn = "^" & varSearch(i, 1) & "\d{0,2}$"
re.Pattern = ptrn
re.IgnoreCase = False
re.Global = True
Set Matches = re.Execute(rngC)
If Matches.Count 0 Then
rngC = Replace(rngC, varSearch(i, 1), varSearch(i, 2))
Exit For
End If
Next
Next
End Sub


Regards
Claus B.
--
Windows10
Office 2016

VBA- Finding texts built from regex patterns
 

Thanks

So I assume the "\d{0,2}$" means a string of 0 to 2 bytes that is numeric and is at the end of the string?

VBA- Finding texts built from regex patterns
 

Hi,

Am Sat, 4 Mar 2017 13:44:05 -0800 (PST) schrieb stainless:

So I assume the "\d{0,2}$" means a string of 0 to 2 bytes that is numeric and is at the end of the string?

yes, you are right.


Regards
Claus B.
--
Windows10
Office 2016