您可以尝试这个 UDF（用户定义函数），并在工作表上使用它作为单元格存放原始字符串的=myReplace(A1)位置。A1

该功能是；

Function myReplace(myRange As Range) As String
    Dim regExp As Object
    
    Set regExp = CreateObject("VBScript.RegExp")
    
    regExp.IgnoreCase = True
    regExp.Global = True
    regExp.Pattern = "!([A-Z0-9]*)"
    
    myReplace = regExp.Replace(myRange.Text, "NOT($1)")

    Set regExp = Nothing
End Function

非正则表达式解决方案：

Function exclamToNot(s As String) As String

    Dim found As Boolean
    Dim i As Integer
    Dim c As String
    Dim t As String
    
    Const terminators As String = ");, "
    
    
    For i = 1 To Len(s)
    
    c = Mid(s, i, 1)
    
 ' Check for exclamation mark
 
    If c = "!" Then
        found = True
        t = t & "not("
        
    ' Check for end of string controlled by exclamation mark
    
    Else
        If found And InStr(terminators, c) > 0 Then
            found = False
            t = t & ")" & c
            
        ' Normal character
        
        Else
            t = t & c
        End If
    End If
            
    Next i
    
    ' Add final bracket if we have reached end of string after exclamation mark
    
    If found Then t = t & ")"
    
    exclamToNot = t

End Function

我想知道您是否可以在工作表公式中使用完全相同的逻辑。这似乎可以正常工作：

=LET(
    c, MID(s, i, 1),
    terminators, ";,) ",
    IF(
        i > LEN(s),
        IF(found, ")", ""),
        IF(
            c = "!",
            "NOT(" & Repex(s, TRUE, i + 1),
            IF(
                AND(found, ISNUMBER(FIND(c, terminators))),
                ")" & c & Repex(s, FALSE, i + 1),
                c & Repex(s, found, i + 1)
            )
        )
    )
)

称为

=repex(A1,false,1)

并保存为 lambda，因此在名称管理器中看起来像这样：

尝试使用递归，尝试处理嵌套：

在名称管理器中定义exclaim_not：

=LAMBDA(str, IF(ISNUMBER(FIND("!", str)), LET(delims, {"("," ",",",";",")"}, left_, LEFT(str, FIND("!", str) - 1), right_, exclaim_not(RIGHT(str, LEN(str) - LEN(left_) - 1)), symbol_len, MIN(IFERROR(SEARCH(delims, right_), LEN(right_) + 1)) - 1, symbol, LEFT(right_, symbol_len), after_symbol, RIGHT(right_, LEN(right_) - LEN(symbol)), left_ & "NOT" & IF(AND(LEFT(after_symbol) = "(", RIGHT(after_symbol) = ")"), IF(LEN(symbol), "(" & symbol & after_symbol & ")", after_symbol), "(" & symbol & ")" & after_symbol)), str))

格式化：

=LAMBDA(str,
    IF(
        ISNUMBER(FIND("!", str)),
        LET(
            delims, {"(", " ", ",", ";",")"},
            left_, LEFT(str, FIND("!", str) - 1),
            right_, exclaim_not(RIGHT(str, LEN(str) - LEN(left_) - 1)),
            symbol_len, MIN(IFERROR(SEARCH(delims, right_), LEN(right_) + 1)) - 1,
            symbol, LEFT(right_, symbol_len),
            after_symbol, RIGHT(right_, LEN(right_) - LEN(symbol)),
            left_ & "NOT" &
                IF(
                    AND(LEFT(after_symbol) = "(", RIGHT(after_symbol) = ")"),
                    IF(LEN(symbol), "(" & symbol & after_symbol & ")", after_symbol),
                    "(" & symbol & ")" & after_symbol
                )
        ),
        str
    )
)

Like使用否定模式替代正则表达式

这种方法不需要逐个检查并将每个字符添加到整个字符串中，

• 通过相应的分隔符将每行代码拆分"!"成标记，
• varStr通过辅助函数识别每个标记的起始字母数字变量字符串getVarStr()，
• Enclose()通过函数执行简单的替换
• 将修改后的标记加入到最终结果中。

示例调用

Sub EncloseExclam()
'Purp: Replace "!" and following variable alpha-num string part with surrounding elements
    With Sheet1.Range("A2:A5")    ' << redefine as needed
        Dim data: data = .Value2
        Dim i As Long
        For i = LBound(data) To UBound(data)
             data(i, 1) = enclose(data(i, 1)) ' << Function enclose()
        Next i
        .Offset(, 1) = data
    End With
End Sub

函数enclose()

Function enclose(ByVal s As String) As String
'Purp.: Execute "!"-replacement by given prefix+"(" ENCL and closing bracket ENCL2
'0) Define constants
Const REPLACECHAR = "!", ENCL = "NOT(", ENCL2 = ")"
'1) Split code into tokens
    Dim tokens:  tokens = Split(s, REPLACECHAR)
'2) Loop through all "!"-split tokens
    Dim i As Long
    For i = 1 To UBound(tokens)  ' i=1, as very first index i=0 can't contain "!"
        'Identify variable alphanum string
        Dim varStr As String: varStr = getVarStr(tokens(i))
        tokens(i) = Replace(tokens(i), varStr, ENCL & varStr & ENCL2)
    Next i
'3) Join tokens as function result
    enclose = Join(tokens, "")
End Function

辅助函数 getVarStr()

通过负面 模式使用负面字符检查，例如（注意之前的"[!A-Z0-9]"感叹号，即识别第一个非字母数字字符）。!A-Z0-9

另一个好处是，可以传递一个可选参数，UCaseOnly:=False以允许变量字符串内使用小写字符。

Function getVarStr(ByVal s As String, Optional UCaseOnly As Boolean = True) As String
'Purp:  get starting alphanumeric string part
    Dim Pattern As String
    Pattern = IIf(UCaseOnly, "[!A-Z0-9]", "[!A-Za-z0-9]")
    Dim i As Long
    For i = 1 To Len(s)
        If Mid(s, i, 1) Like Pattern Then
            s = Left(s, i - 1): Exit For
        End If
    Next i
    getVarStr = s
End Function

Function ConvertString(s As String) As String
    With CreateObject(Class:="VBScript.RegExp")
        .Pattern = "!([A-Z0-9]{1,})"
        .Global = True
        ConvertString = .Replace(s, "NOT($1)")
    End With
End Function