Replacing Bracketed Text In String Using Wildcard Characters in VBA

[Wookiee]

Hello, MrExcel Community! I have a question which I have so far been unable to answer by googling. I hope you can help.

I have VBA code which is supposed to clean up some undesirable elements from text strings. In some cases, there will be a notation between brackets in the text strings and I would like to remove the entire bracketed segment of the string, but I have been unable to do so.

To illustrate, a couple of examples would be:
"Darth Vader [Empire]" becomes simply "Darth Vader"
"Han Solo [Rebellion]" converts to "Han Solo"

I even tried recording a macro where I used a wildcard in my Search/Replace. Although the recorded code doesn't seem significantly different from what I had already written, my code doesn't strip off the bracketed text.

Recorded Macro

Selection.Replace What:="
[*]", Replacement:=""

My Code

strSource = Replace(strSource, "
[*]", "")

I'm assuming that the difference in the functionality might have to do with the fact that the recorded macro worked on cells within a worksheet, whereas my code is attempting to manipulate a string which exists solely in the VBA environment. Can anyone point out what I'm missing or provide an alternate method?

Thanks, all!

 

[MickG]

Try this for strings in column "A".
This code should cater for multiple "[***]" in each row.

[COLOR="Navy"]Sub[/COLOR] MG17Jun36
[COLOR="Navy"]Dim[/COLOR] Dn [COLOR="Navy"]As[/COLOR] Range, Rng [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] str [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] fd [COLOR="Navy"]As[/COLOR] Boolean
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    fd = True
    str = ""
    [COLOR="Navy"]For[/COLOR] n = 1 To Len(Dn.Value)
        [COLOR="Navy"]If[/COLOR] Dn.Characters(n, 1).Text = "[" [COLOR="Navy"]Then[/COLOR] fd = False
            [COLOR="Navy"]If[/COLOR] fd [COLOR="Navy"]Then[/COLOR]
                str = str & Dn.Characters(n, 1).Text
            [COLOR="Navy"]End[/COLOR] If
         [COLOR="Navy"]If[/COLOR] Dn.Characters(n, 1).Text = "]" [COLOR="Navy"]Then[/COLOR] fd = True
    [COLOR="Navy"]Next[/COLOR] n
Dn.Value = str
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Wookiee]

Thanks, MickG! Although I had hoped to avoid having to loop through each character in the string, your code does accomplish what I wanted to do. I really appreciate your assistance.

While I was awaiting a reply, I continued trying different ways to approach the problem. In the case of the strings I'll be reviewing, the only times bracketed notations should appear is at the end of the string. So I decided to scan the string for an opening bracket, then scrub all the extraneous text off from that point. Here is what I came up with:

Function ScrubTheNaughtyBits _
    (strSource As String)
'Removes Unwanted Characters And Bracketed Notes From String
'Crafted 17 Jun 2016 by Jason B White


Dim bytBracket As Byte

strSource = Replace(strSource, "'", "")
strSource = Replace(strSource, "/", " ")
strSource = Replace(strSource, "&", " ")

'My Original (Non-Working) Code:
'strSource = Replace(strSource, "
[*]", "")

'Revised (Working) Code:
bytBracket = InStr(strSource, "[")
If bytBracket > 0 Then
   strSource = Trim(Left(strSource, bytBracket - 1))
End If

ScrubTheNaughtyBits = strSource


End Function

Thank you.