自定义VBA函数开发需求：删除字符串中的括号及内部内容

No worries, let's get that custom VBA function working for you! The issue with your current DelPar function is likely one of a few common pitfalls: not correctly handling range inputs, missing regex settings to replace all occurrences, or not cleaning up extra spaces left after removing parentheses.

Here's a robust, tested solution that does exactly what you need:

Function DelPar(Source As Variant) As String
    Dim regex As Object
    Dim cleanedText As String
    
    ' Handle both single-cell range inputs and direct string values
    If TypeName(Source) = "Range" Then
        cleanedText = Source.Value
    Else
        cleanedText = CStr(Source)
    End If
    
    ' Use late binding for regex (no need to set manual references)
    Set regex = CreateObject("VBScript.RegExp")
    
    With regex
        .Global = True ' Ensure all occurrences are replaced, not just the first
        .Pattern = "\(.*?\)" ' Match any parentheses and everything inside (non-greedy match)
    End With
    
    ' Remove all parentheses and their contents
    cleanedText = regex.Replace(cleanedText, "")
    
    ' Clean up extra spaces: replace multiple spaces with one, then trim edges
    regex.Pattern = "\s+" ' Match one or more consecutive spaces
    cleanedText = Trim(regex.Replace(cleanedText, " "))
    
    ' Return the final cleaned string
    DelPar = cleanedText
End Function

How to Use This Function: #

1. Open your Excel workbook, press Alt + F11 to launch the VBA Editor.
2. Insert a new module (Right-click your workbook in the Project Explorer > Insert > Module).
3. Paste the code above into the module.
4. Return to Excel, and in any cell, enter =DelPar(A1) where A1 contains your input string (e.g., "508 (7S9 5DU) 609 (609)"). You’ll get the desired output: "508 609".

Key Details About This Solution: #

• Late Binding: Using CreateObject("VBScript.RegExp") means you don’t have to manually enable the Microsoft VBScript Regular Expressions library, avoiding "missing reference" errors.
• Non-Greedy Match: The .*? in the regex pattern ensures we match from the first ( to the closest ), which is perfect for your example (no nested parentheses).
• Space Cleanup: After removing parentheses, we eliminate redundant spaces and trim edges to produce a clean, formatted output.

Note for Nested Parentheses (If Needed): #

If your data ever includes nested parentheses (like "A (B (C) D) E"), the regex method won’t handle this perfectly. For that scenario, we’d need a loop-based solution to parse nested levels, but since your example doesn’t involve this, the regex approach is ideal.

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