使用正则表达式提取字符串开头大写单词的VBA实现问题

Hey there! Let's get that VBA code working to extract those leading uppercase words exactly how you need it. The key is a precise regex pattern that targets only the consecutive uppercase words at the start of your string, stopping before any non-uppercase/non-space characters.

Complete VBA Code #

Sub ExtractUPPERCASE()
    Dim re As Object, mc As Object
    Dim r As Range, c As Range
    Dim s As String
    
    ' Initialize regex object
    Set re = CreateObject("VBScript.RegExp")
    With re
        .Pattern = "^[A-Z]+(?:\s+[A-Z]+)*" ' Critical regex pattern
        .Global = False ' We only need the first match (start of string)
        .IgnoreCase = False ' Case-sensitive to target only uppercase
    End With
    
    ' Set your target range (adjust this to your actual range, e.g., Range("A2:A100"))
    Set r = Application.InputBox("Select the range containing your strings", Type:=8)
    
    ' Loop through each cell in the range
    For Each c In r
        s = Trim(c.Value) ' Remove leading/trailing spaces first
        If s <> "" Then
            Set mc = re.Execute(s)
            If mc.Count > 0 Then
                ' Write result to the adjacent column (change offset as needed)
                c.Offset(0, 1).Value = mc(0).Value
            Else
                c.Offset(0, 1).Value = "" ' No leading uppercase words found
            End If
        Else
            c.Offset(0, 1).Value = "" ' Empty cell
        End If
    Next c
    
    ' Cleanup
    Set re = Nothing
    Set mc = Nothing
    Set r = Nothing
    
    MsgBox "Extraction complete!", vbInformation
End Sub

Regex Pattern Breakdown #

Let's break down the ^[A-Z]+(?:\s+[A-Z]+)* pattern to understand how it works:

• ^: Anchors the match to the start of the string (so we only capture leading content)
• [A-Z]+: Matches one or more uppercase letters (this captures the first uppercase word)
• (?:\s+[A-Z]+)*: A non-capturing group that matches zero or more instances of:
  • \s+: One or more spaces (separating words)
  • [A-Z]+: Another uppercase word
• This ensures we grab all consecutive uppercase words at the start, stopping as soon as we hit a character that's not uppercase or a space (like underscores, numbers, lowercase letters, etc.)

How to Use #

1. Open your Excel workbook
2. Press Alt + F11 to open the VBA Editor
3. Insert a new module (Right-click your workbook in the Project Explorer → Insert → Module)
4. Paste the code above into the module
5. Run the macro (Press F5 or use the "Run" button)
6. Select the range containing your source strings when prompted
7. The extracted uppercase words will appear in the column immediately to the right of your selected range

Tested with Your Examples #

内容的提问来源于stack exchange，提问作者NoobMaster101