1

I'm trying to replace any text in square brackets, including them – "[]" in a cell by emptiness with UDF:

Function RMV(iCell As Range) As Variant
RMV = Replace(iCell.Value, "[*]", "")
End Function

But I guess the asterisk ("*") does not work here.

Community
  • 1
  • 1
FL.Alx
  • 71
  • 1
  • 6
  • Possible duplicate of [Regular expression substring replacement in Microsoft Excel](https://stackoverflow.com/q/999983/11683) – GSerg Sep 28 '17 at 12:28
  • @GSerg - Thanx for the idea, but I have no experience of use RegExp. – FL.Alx Sep 28 '17 at 12:34
  • You have tried to use `*` as a pattern for "everything." That *is* regexp. – GSerg Sep 28 '17 at 12:38
  • @GSerg - OK, I understand, but why this works correctly: Selection.Replace What:="[*]", Replacement:="" ? – FL.Alx Sep 28 '17 at 12:43
  • Because Excel interface provides [rudimentary support](https://superuser.com/q/630692/52365) for regular expressions, and that is the method that is behind it. You can't expect same behaviour from other methods that also happen to be named `Replace`. – GSerg Sep 28 '17 at 13:54
  • @GSerg - Thank you for the explanations! – FL.Alx Sep 28 '17 at 14:06

2 Answers2

2

To use reGex, you could use this as Function, remember to enable Microsoft VBScript Regular Expression 5.5

Function RMV(iCell As Range) As Variant
    Dim regEx As Object: Set regEx = CreateObject("VBScript.RegExp") 'If Error Set regEx  = New regexp
    Dim strPattern As String
    Dim strInput As String
    Dim strReplace As String
    Dim strOutput As String

    strPattern = "\[\]|\[.+?\]|$"

    If strPattern <> "" Then
        strInput = CStr(iCell.Value)
        strReplace = ""

        With regEx
            .Global = True
            .MultiLine = False
            .IgnoreCase = False
            .Pattern = strPattern
        End With

        If regEx.test(strInput) Then
            RMV = regEx.Replace(strInput, strReplace)
        Else
            RMV = "Not matched"
        End If
    End If
End Function

Where the ReGex test uses the \[\]|\[.+?\]|$ expression. I am also new to Regex, so this expression can be optimized.

danieltakeshi
  • 887
  • 9
  • 37
0

In sub procedure, that works.

Sub RMV(rng As Range)
rng.Replace "[*]", ""
End Sub

Sub Test()
RMV Range("A2")
End Sub
Dy.Lee
  • 7,527
  • 1
  • 12
  • 14