Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 7
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 7
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Multiple Regex Patterns Geoff K Excel Programming 1 October 18th 09 02:08 PM
Regex exception Geoff K Excel Programming 4 October 17th 09 11:53 AM
Regex techniques Dave Runyan Excel Programming 5 April 28th 07 12:17 AM
RegEx Replacement patterns CodeSponge[_2_] Excel Programming 2 February 5th 05 08:39 AM
Regex Question William Barnes Excel Programming 5 January 2nd 04 12:57 PM


All times are GMT +1. The time now is 06:46 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"