自动过滤号码

Sub AutoFilterOnNumber()

    ' Define constants.
    
    Const F_COLUMN As Long = 29
    Const F_CRITERION As String = "4"
    Const F_DELIMITER As String = "*"
    
    ' Reference the table range.
    
    Dim rg As Range
    
    With ActiveSheet ' improve!
        If .FilterMode Then .ShowAllData ' clear filters
        If .AutoFilterMode Then .AutoFilterMode = False ' turn off auto filter
        Set rg = .Range("A1").CurrentRegion
    End With
    
    ' Write the values from the critical column of the range to an array.
    
    Dim rCount As Long: rCount = rg.Rows.Count - 1
    Dim Data():
    Data = rg.Columns(F_COLUMN).Resize(rCount).Offset(1).Value
        
    ' Write the matching strings to the keys (a 1D array) of a dictionary.
    
    ' Define the dictionary.
    Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")
    
    ' Declare variables to be introduced in the For...Next loop.
    Dim SubStrings() As String, r As Long, rStr As String
    
    ' Loop through the elements of the array.
    For r = 1 To rCount
        ' Convert the current value to a string and store it in a variable.
        rStr = Data(r, 1)
        If Len(rStr) > 0 Then ' is not blank
            ' Split the string into an array.
            SubStrings = Split(rStr, F_DELIMITER)
            ' Attempt to match the criterion in the split array.
            If IsNumeric(Application.Match(F_CRITERION, SubStrings, 0)) Then
                If Not dict.Exists(rStr) Then ' not in the dictionary
                    dict(rStr) = Empty
                'Else ' already in the dictionary; do nothing
                End If
            'Else ' criterion not found, it's an error value; do nothing
            End If
        'Else ' is blank; do nothing
        End If
    Next r
    
    ' Filter the table range.
    
    If dict.Count > 0 Then ' there are rows to be filtered
        ' Use the keys (a 1D array) of the dictionary
        ' with 'xlFilterValues' to filter the data.
        rg.AutoFilter F_COLUMN, dict.Keys, xlFilterValues
    'Else ' no rows to be filtered; do nothing
    End If
    
End Sub

如果你喜欢使用helper列，这可以通过一个公式来完成：

=IF(ISERROR(FIND("x4x","x"&SUBSTITUTE(A2,"*","x")&"x")),FALSE,TRUE)

然后简单地过滤B列结果。
输出：

如果helper列正常：
创建一个公式为=IF(ISERROR(SEARCH("~*4~*", "*"&A2&"*")),"NO","YES")的列（假设数据从A2开始）。
原始单元格内容用额外的前导和尾随星号括起来（例如4*1将是*4*1*）。现在我们可以确定您感兴趣的单元格包含字符串*4*。你可以使用这个字符串并设置一个自动过滤器，但是，它有点难看，因此我使用SEARCH和IF来检查helper列是否包含*4*并返回YES或NO。现在很容易过滤。
请注意，您必须使用前导波浪符来屏蔽星号，以防止Excel将其视为野生字符（在上面的公式或自动筛选条件中使用它时都是如此）。这就是为什么搜索函数的第一个参数是"~*4~*"，看起来很奇怪。