Regex-pattern

batman1 · #1 01-03-2025, 10:19 AM

Quote:

Originally Posted by gmaxey

Batman1,

Thanks for the post. I see that your suggested pattern "requires" two or more words between the commas to return a match. So

, John, won't match but
, John smith, would.

If you read post #1, #2, #3, #4 you will understand why I wrote: Last name-first name with 2, 3, 4 ... parts, so there should be at least 2 parts.

Quote:

A RegEx pattern master I am not. I wonder if it is possible to construct the pattern such that the last word (the word before the ending comma) must be captitalized e.g.,

,Gerd van Ackerman, matches
,Gerd van ackeramn, would not

With my code ",Gerd van Ackerman," does NOT match. After the first comma there MUST be a space. This is a requirement of this thread.

The following code meets your requirements. I can't write a simpler pattern.

Code:

Sub ScratchMacro()
Dim RegEx As Object, Matches As Object, Match As Object
    Set RegEx = CreateObject("VBScript.RegExp")
    With RegEx
        .Global = True
        .Pattern = ", [A-Z][^, ]+( [^, ]+)*( [A-Z][^,]+)?,"
    End With
    Set Matches = RegEx.Execute(ActiveDocument.Range.text)
    For Each Match In Matches
        Debug.Print Match.Value
    Next
End Sub

gmaxey · #2 01-03-2025, 11:33 AM

Batmat1,

Yes, correct. Omitted space after first comma was typo. Now, testing with your latest version: ", [A-Z][^, ]+( [^, ]+)*( [A-Z][^,]+)?,"
when applied to the following example returns the same matches as my last version: ",\s{A-Z][^,]*,"

Specifically the last instance ", John smith," is returned as a match. How could we prevent that? If you don't mind, can you explain what each part of your pattern is intended to perform?

For others following, with mine it is
1. "," match a comma
2. "\s" match a space
3. "[A-Z]" match a capital letter A to Z
4. "[^,]*" match any characters excluding a comma one or more times
5. "," match a comma

batman1 · #3 01-03-2025, 02:36 PM

Quote:

Originally Posted by gmaxey

Batmat1,

Specifically the last instance ", John smith," is returned as a match. How could we prevent that?

Code:

Sub ScratchMacro()
Dim RegEx As Object, Matches As Object, Match As Object
    Set RegEx = CreateObject("VBScript.RegExp")
    With RegEx
        .Global = True
        .Pattern = ", [A-Z][^, ]+(|( [^, ]+)* [A-Z][^,]+),"
    End With
    Set Matches = RegEx.Execute(ActiveDocument.Range.text)
    For Each Match In Matches
        Debug.Print Match.Value
    Next
End Sub

The author of the thread did not really provide criteria for the input data. If we have requirements for the form of the results, we must also specify the form of the input data. If the input data can be any, we must provide all characters accepted between commas. See that the given code finds the result ", Beate 123-van4 Ackeren," and that is not a surname and name, right?

The code below accepts only characters in CONST characters. Tested with data as in the picture.

Code:

Sub ScratchMacro()
Const characters As String = "[A-Za-zü\-]"
Dim RegEx As Object, Matches As Object, Match As Object
    Set RegEx = CreateObject("VBScript.RegExp")
    With RegEx
        .Global = True
        .Pattern = ", [A-Z]" & characters & "+(|( " & characters & "+)* [A-Z]" & characters & "+),"
    End With

    Set Matches = RegEx.Execute(ActiveDocument.Range.text)
    For Each Match In Matches
        Debug.Print Match.Value
    Next
End Sub

Quote:

If you don't mind, can you explain what each part of your pattern is intended to perform?

For others following, with mine it is
1. "," match a comma
2. "\s" match a space
3. "[A-Z]" match a capital letter A to Z
4. "[^,]*" match any characters excluding a comma one or more times
5. "," match a comma

2. "\s" match a comma, TAB, form-feed, .... equivalent with "[ \f\n\r\t\v]"

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Capture group in RegEx	alex100	Word VBA	1	01-02-2021 02:39 PM
Regex over 700 matches in a long doc	totoMSOF	Word VBA	19	03-11-2019 01:28 PM
Using VB Regex feature, I tried to replace 'the' and 'this' with 'that' but got screwed	abdan	Word VBA	3	01-18-2019 09:38 PM
Macro help regex	subspace3	Word VBA	1	10-15-2014 09:53 AM
Regex in Word: Replaced strings are in disorder	chgeiselmann	Word	0	04-26-2009 11:33 AM