How to properly use wildcard charlist in VBA Excel

Question

I have a list of data where I have to delete some information in parenthesis, but some should stay.

It looks like that (in separate cells):

A
Aqua (Water) (100%)
Parfum (Fragrance) (90%)
Some Plant (Latinname) Extract (76%)

And I need to have:

A
Aqua (100%)
Parfum (90%)
Some Plant Extract (76%)

I used such code in vba:

Cells.Replace What:=" ([A-Z]*)", replacement:="", lookat:=xlPart

but it doesn't work.

Using

Cells.Replace What:=" (*)", replacement:="", lookat:=xlPart

will delete all data in parenthesis. The case seems easy, but I cannot find the solution. I tried also:

Cells.Replace What:=" ({A-Z]*[a-z])", replacement:="", lookat:=xlPart

or:

Cells.Replace What:=" ([A-Z]*[! %])", replacement:="", lookat:=xlPart

But it also didn't work.

EDIT

The case is a little bit complicated. Unfortunately, the data pattern may vary. It is not always "WORD (WORD_TO_DELETE) (PERCENTAGE)" but it is like:

A
Name1 (10%)
Name2 (Data_to_delete) RestOfName2 (15%)
Name3 (Data_to_delete) RestOfName3 (20%), Name4 (Another_data_to_delete) RestOfName4 (25%)

So I used (of course temporarily) that:

For i = 1 To 3
    Sheets("Sheet1").Cells(i, 1).Replace What:=" (A*)", replacement:="", lookat:=xlPart
Next i

For i = 1 To 3
    Sheets("Sheet1").Cells(i, 1).Replace What:=" (B*)", replacement:="", lookat:=xlPart
Next i

etc.

And it works perfectly, but look very ugly. I think the regular expressions may be a solution, but I need a little more time to check it.

I know, the solution is to change the structure of the source database and change the string data to some nicer format, but unfortunately I don't have a permission to modify that.

EDIT2

Case closed. RegEx is the solution. The cell A3 is "Lameria Borea (Latinname) Extract (76%), Aqua (Water) (<45%)". The code below will do the job:

Sub test()

Dim i As Integer
Dim strPattern As String: strPattern = " \([A-Z][a-z]*\)"
Dim strReplace As String: strReplace = ""
Dim regEx As New RegExp
Dim strInput As String
Dim Myrange As Range

Set Myrange = ActiveSheet.Range("A3")

    If strPattern <> "" Then
        strInput = Myrange.Value

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

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

End Sub

It needs to be a little bit polished to fulfil my goal, but it will resolve all the cases I mention in the first edit.

@chris neilsen Don't agree. Maybe Range.Replace does not support charlist? Beacuse using wildcard "*" works as it should work. — Pu6, Mar 07 '22 at 09:41
@Pu6 Fyi You might be interested in my late edit to my original answer: I managed to successfully modify your attempts with *wild cards* for your case even when using `Range.Replace()`. — T.M., Mar 07 '22 at 15:16
@T.M. Thanks for both answers. Unfortunateley the case is more complicated. First of all, both of the solutions cut all of the string part after the first word included in parenthesis. So when we have "Some Plant (Latinname) Extract (76%)" only "Some Plant (76%)" left, but I need "Some Plant Extract (76%)". I have edited the question, so it would clarify what I mean. — Pu6, Mar 08 '22 at 07:25
Both posts (including wildcards) answered the original pattern `"WORD (WORD_TO_DELETE) (PERCENTAGE)"`, the late correction to "Some Plant (Latinname) Extract (76%)" equalling `"WORD (WORD_TO_DELETE) **WORD** (PERCENTAGE)"` was not evident. Feel free to upvote one or both if you consider them helpful though, at least as input for other users. @Pu6 — T.M., Mar 08 '22 at 08:28
@T.M. You're right. I think all answers added something useful in the subject. Thanks for your help. — Pu6, Mar 08 '22 at 08:32

T.M. · Accepted Answer · 2022-03-07T14:57:49.153

A) Array/Split approach

Applying a Split() action on a variant 2-dim datafield array you might code as follows:

Option Explicit                          ' code module head

Sub NamesAndPercentageOnly()
    Dim rng As Range                     ' declare fully qualified range reference when setting to memory
    Set rng = Sheet1.Range("A1:A3")      ' << change to wanted sheet's Code(Name)/Range
'1) assign data to 1-based 2-dim array
    Dim data As Variant
    data = rng.Value
'2) rearrange 1st and 3rd token after split action
    Dim i As Long, tokens As Variant
    For i = 1 To UBound(data)
        tokens = Split(data(i, 1), "(")          ' split into zero-based(!) 1-dim array
        data(i, 1) = tokens(0) & "(" & tokens(2) ' rearrange (1st~>index 0, 3rd~>index 2)
    Next i
'3) write results to next column
    rng.Offset(, 1) = data
End Sub

Note If the percentage isn't invariably the 3rd token, but appears as last one you would refer to the tokens' Upper Boundary (Ubound(tokens) instead of the fixed index 2:

   data(i, 1) = tokens(0) & "(" & tokens(Ubound(tokens))

B) ... and a late answer to the (limited) usability of wild cards

Yes, there is a possible use of wild cards regarding your fixed pattern.

If you want to stick to the (somehow limited) Range.Replace() method, you could to change the What pattern to "(*)*(" indicating the precise start/end/start-brackets together with a left bracket replacement (thus avoiding the entire right side to be cut):

    Sheet1.Range("A1:A3") .Replace What:="(*)*(", Replacement:="(", MatchCase:=False, _
SearchOrder:=xlByColumns, LookAt:=xlPart, SearchFormat:=False, ReplaceFormat:=False

score 1 · Answer 2 · answered Mar 07 '22 at 10:00

You may benefit from text functions in VBA to extract the part you want because your data follows a pattern:

Sub test()
Dim MyLeft As Long
Dim MyRight As Long
Dim rng As Range

For Each rng In Range("A1:A3")
    MyLeft = InStr(1, rng.Value, " (") - 1
    MyRight = InStr(1, rng.Value, ")")
    
    Debug.Print Left(rng.Value, MyLeft) & Right(rng.Value, Len(rng.Value) - MyRight)
Next rng


End Sub

The output I get:

Please, notice this will work only if your data follows the same pattern: TARGET_TEXT_1 (DELETE_THIS) (TARGET_TEXT2%)

How to properly use wildcard charlist in VBA Excel

2 Answers2