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.

CodePudding user response：

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%)

CodePudding user response：

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