如何在VBAExcel中正確使用通配符字串列

我有一個資料串列，我必須洗掉括號中的一些資訊，但有些應該保留。

它看起來像這樣（在單獨的單元格中）：

我需要：

我在vba中使用了這樣的代碼：

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

但它不起作用。

使用

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

將洗掉括號中的所有資料。這個案子看起來很簡單，但我找不到解決辦法。我也試過：

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

要么：

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

但它也沒有奏效。

編輯

這個案子有點復雜。不幸的是，資料模式可能會有所不同。它并不總是“WORD（WORD_TO_DELETE）（PERCENTAGE）”，但它就像：

所以我使用（當然是暫時的）那個：

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

等等。

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.

uj5u.com熱心網友回復：

A）陣列/拆分方法

Split()對變體 2-dim 資料欄位陣列應用操作，您可以撰寫如下代碼：

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

注意如果百分比并非總是第三個標記，而是顯示為最后一個標記，則您將參考標記的U pper Bound ary (Ubound(tokens)而不是固定索引2：

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

B）......以及對通配符（有限）可用性的遲到答案

是的，關于您的固定模式，可能會使用通配符。

如果您想堅持（某種程度上受限）

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

我得到的輸出：

請注意，這僅在您的資料遵循相同模式時才有效：TARGET_TEXT_1 (DELETE_THIS) (TARGET_TEXT2%)

標籤：excel vba

上一篇：如何在excel中連接2個單元格

下一篇：組合COUNTIF公式