Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple Regex Patterns | Excel Programming | |||
Regex exception | Excel Programming | |||
Regex techniques | Excel Programming | |||
RegEx Replacement patterns | Excel Programming | |||
Regex Question | Excel Programming |