[SOLVED] Enclosing a variable string of values with a bracket within a larger string all contained within a single cell

Enclosing a variable string of values with a bracket within a larger string all contained within a single cell

I have cells which contain a ! followed by a Alpha Numeric String which varies in length.

I want to change the ! to NOT( which is relatively easy, the part I am struggling with is the closing bracket after the Alpha Numeric String

!AlphaNumericString becomes NOT(AlphaNumericString)

Cell examples before change,
AND(!BI,OR(Z1,Z2,Z102,Z103,Z104,Z306))

!L1M23 OR (NP001 AND !CA);
Z200 AND !VP100;
!N001L010D132

I want those cells to become,
AND(NOT(BI),OR(Z1,Z2,Z102,Z103,Z104,Z306))

NOT(L1M23) OR (NP001 AND NOT(CA));
Z200 AND NOT(VP100);
NOT(N001L010D132)

The Exclamation is always followed by 1 character, then it would be a series of alpha numeric characters and that Exclamation string will always end with one of the following

Space
)
;
,
Or it could end with the last character in the cell,

As per the above examples

Hope that makes sense.

I would prefer a Macro so it is backward compatible from EXCEL 365 back to 2010, but if there is simple formula, awesome.

Thanks mbart67

Solution

You can try this UDF (User Defined Function) and use it on the sheet as =myReplace(A1) where cell A1 is housing your original string.

The function is;

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