VBA code for a regex find and replace

[mrwul62]

Up front: I have no knowledge of VBA.

I'd like to do a multiple find-and-replace using regex.

Select column, run macro on filled cells.

Macro would find a regex pattern and replace it.

Very simple example:
contents cell: 'some text on january 18 2017'

find: ^(.+)january\s(\d{2})\s(\d{4})
repl: $1$2-01-$3

result: 'some text on 18-01-2017'

find: ^(.+)april\s(\d{2})\s(\d{4})
repl: $1$2-04-$3

etc.

same for all months and in various methods writing, like 18 january 2017

There will be quite a number of regex-finds and replaces then.
The regex as such isn't the issue, that's something I'll sort out myself.


I'd like to have kind of 'base VBA-code' so I can add new find-and-replaces myself.
A kind of 'structure' so to say.

Again, I have no knowledge of VBA.

Thanks
=

 

[pgc01]

Up front: I have no knowledge of VBA.

The regex as such isn't the issue, that's something I'll sort out myself.

I'd like to have kind of 'base VBA-code' so I can add new find-and-replaces myself.
A kind of 'structure' so to say.

Hi

If I understand correctly you have experience with regex's, you just need to know how you use them in vba.

See if this simple example helps.

The code runs the regex.replace() in the cells from A2, down till the last non-empty cell

Sub TestRegex()
Dim regex As Object
Dim r As Range, rC As Range

' cells in column A
Set r = Range("A2", Cells(Rows.Count, "A").End(xlUp))

Set regex = CreateObject("VBScript.RegExp")
regex.Pattern = "^(.+)january\s(\d{2})\s(\d{4})"

' loop through the cells in column A and execute regex replace
For Each rC In r
    If rC.Value <> "" Then rC.Value = regex.Replace(rC.Value, "$1$2-01-$3")
Next rC
    
End Sub

 

[mrwul62]

THANK you so much for the quick reply. Really appreciated. I have been puzzling and trying for.. I don't know how long.

That is the idea.

The thing is that there will be multiple find-and-replaces.
Where do I get them in there?
Just adding new lines with regex.pattern?
in my example above I mentioned April as a 2nd find and replace.

Do I not have to number the patterns and replaces?
so, like regex.pattern01 replace with value01, regex.pattern02 replace with value02, etc.

Thanks again.
=

 

[pgc01]

Hi

I think it would be easier if you have just 1 pattern, that checks for all the months in a format that you define.
If the pattern is matched, then check which is the month, replace it with the corresponding number and compose the date string.

In your example:

january 18 2017

this case your format is:

Month_Name Day Year

for this format, using the code I posted for january, I changed it to accept also the other months.

Test:

Sub TestRegEx()
Dim regex As Object, regexMatches As Object
Dim r As Range, rC As Range
Dim sMonths As String, sMonth As String

sMonths = "january|february|march|april|may|june|july|august|september|october|november|december"

' cells in column A
Set r = Range("A2", Cells(Rows.Count, "A").End(xlUp))

Set regex = CreateObject("VBScript.RegExp")
regex.Pattern = "^(.+)(\d{2})\s(" & sMonths & ")\s(\d{4})"
regex.IgnoreCase = True

' loop through the cells in column A and execute regex replace
For Each rC In r
    Set regexMatches = regex.Execute(rC.Value)
    If regexMatches.Count > 0 Then
        sMonth = Format(Application.Match(regexMatches(0).submatches(2), Split(sMonths, "|"), 0), "00")
        rC.Value = regex.Replace(rC.Value, "$1$2-" & sMonth & "-$4")
    End If
Next rC
    
End Sub

 

[pgc01]

Sorry the pattern was for another test.

For the last post try:

Sub TestRegEx()
Dim regex As Object, regexMatches As Object
Dim r As Range, rC As Range
Dim sMonths As String, sMonth As String

sMonths = "january|february|march|april|may|june|july|august|september|october|november|december"

' cells in column A
Set r = Range("A2", Cells(Rows.Count, "A").End(xlUp))

Set regex = CreateObject("VBScript.RegExp")
regex.Pattern = "^(.+)\s(" & sMonths & ")\s(\d{2})\s(\d{4})"
regex.IgnoreCase = True

' loop through the cells in column A and execute regex replace
For Each rC In r
    Set regexMatches = regex.Execute(rC.Value)
    If regexMatches.Count > 0 Then
        sMonth = Format(Application.Match(regexMatches(0).submatches(1), Split(sMonths, "|"), 0), "00")
        rC.Value = regex.Replace(rC.Value, "$1$3-" & sMonth & "-$4")
    End If
Next rC
    
End Sub

 

[mrwul62]

I understand.
Thank you very much for the help and taking the time for this.
Am afraid though there is no uniform way to approach the various methods, meaning to say that for all different 'date formats' probably a separate regex should be defined.
(like Januar[iy] 13 2017 or o[ck]tober 5 2017 or o[ck]t 10 2017)
Suggest to let this rest: it is getting too complex.

Again thanks for the help!

=

 

[pgc01]

You're welcome.

 

[welshgasman]

Don't give up now!

If you are clever enough to understand the regex expressions (I'm still trying to get my head around them ) then you can do this.

I would approach it as follows.

You have been given the code to loop through the range.

On another sheet put each reg expression in a cell, let's say column A.

Make an outer loop for the current loop and walk through every cell in column A until a blank cell is found, much the same as you are doing for the target range.

So all you have to do is change sheets and get the next expression, return to the data sheet and run your code.

I'm sure pgc01 would be able to knock this up in a heartbeat, me, it would take a while as I am not that proficient with VBA.