使用自动筛选时，除了完全匹配之外，无法使用2个以上的条件。那么，使用帮助器列如何？例如，使用正则表达式可以非常明确。以下是一个示例：

Public Function RegexMatch(str, pat) As Boolean

With CreateObject("vbscript.regexp")
    .pattern = pat
    RegexMatch = .Test(str)
End With

End Function

在工作表级别使用以下命令调用：

=RegexMatch(C2,"\b[Mm]od(?!erate).*\b[hH]\b")

• \b-Assert后面的内容之前没有其他单词字符的单词边界;
• [Mm]od-大写/小写“m”后跟“od”;
• (?!erate)-Assert位置的负前瞻不跟“erate”;
• .*-除换行符以外的任何（0+，贪婪）字符;
• \b[hH]\b-匹配大写/小写字母“h”，并Assert它是具有单词边界的单个字母子字符串。

现在你可以在你的自动过滤器中引用这些布尔值了。

我也认为Autofilter不能做你需要的。但是AdvancedFilter应该做...
AdvancedFilter的一个缺点是它需要一个条件Range，这个条件不能用数组来代替，但是基于数组，这样的范围可以被创建，设置和删除，请尝试下面的代码：

Sub AdvFilter_Critr()
   Dim ws As Worksheet, LRow As Long, LCol As Long, rng As Range, rngCrit As Range, arrCrit
   Const filtCol As Long = 3 'column to be filtered
   
   'build the criteria array, based on what to create a criteria range, which is able to admit more than two criteria strings using wildcard:
    arrCrit = Array("=""=*Mod h*""", "=""=*Mod. h*""", "=""=*Mod?? H*""", "=""=*mod??? h*""")
   
    Set ws = ActiveSheet
    If ws.AutoFilterMode Then ws.cells.AutoFilter 'completely clear AutoFilter...
    If ws.FilterMode Then ws.ShowAllData              'clear AdvancedFilter...
    
     LCol = ws.cells(2, ws.Columns.count).End(xlToLeft).column 'last col on the second row
     LRow = ws.cells(ws.rows.count, "A").End(xlUp).Row
      Set rng = ws.Range("A2:J" & LRow)
      ws.cells(3, LCol + 2).Value = rng.cells(filtCol).Value 'copy the criteria column header
      ws.cells(3, LCol + 2).Offset(1).Resize(UBound(arrCrit) + 1).Value = Application.Transpose(arrCrit) 'build the criteria range
      Set rngCrit = ws.cells(3, LCol + 2).Resize(UBound(arrCrit) + 2) 'set the criteria range
       
      rng.AdvancedFilter xlFilterInPlace, rngCrit 'place the advanced filter
    
      rngCrit.Clear 'clear the helper criteria range
End Sub

上面的代码假定标题行是第二行！

正如我所评论的，这也可以使用Power Query来完成，Windows Excel 2010+和Excel 365（Windows或Mac）中提供
使用增强查询的步骤

• 选择数据表中的某个单元格
• Data => Get&Transform => from Table/Range或from within sheet
• PQ编辑器打开时：Home => Advanced Editor
• 记录第2行中的表名称
• 将下面的M代码粘贴到您看到的位置
• 将第2行中的Table名称改回最初生成的名称。
• 阅读评论并探索Applied Steps以了解算法
• • 我相信注解已经足够清楚，如果需要，您可以添加其他条件。*

let

//change next line to reflect actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID Description", type text}}),
  
    #"Select Rows" = Table.SelectRows(#"Changed Type", (rw)=>
      let
  
      //replace punctuation with <space>
            t1 = Text.Replace(rw[ID Description],"."," "), 

      //put spaces around the ampersand and make string all lower case
      //  since PQ is case sensitive
            t2 = Text.Lower(Text.Replace(t1,"&"," & ")),

      //split into a list of substrings by space
            split = Text.Split(t2," "),

      //select all words that start with mod and do not contain "erate"
            #"mod variations" = List.Select(split, each     
                  Text.StartsWith(_,"mod") and not Text.Contains(_, "erate")),

      //find position of first mod or valid mod variant
      //  will return -1 if not found
            #"First mod" = List.PositionOfAny(split,#"mod variations",Occurrence.First),

      //find position of last "h"
            #"last h" = List.PositionOf(split,"h",Occurrence.Last)
      in 

      //test to see if string is valie
            #"First mod">-1 and #"last h" > #"First mod")

in
    #"Select Rows"

这并不是一个完美的解决方案，因为它涉及到在过滤源数据之前编辑源数据。但是，如果您同意在数据中将“C&H”更改为“C & H”（这样H总是被空格包围或位于字符串的末尾）

Option Explicit
Option Compare Text
 
Sub Filter_Critr()

Const critr1 As String = "*Mod* H *"
Const critr2 As String = "*Mod* H"
 
Dim ws As Worksheet, LRow As Long, rng As Range
Set ws = ActiveSheet
 
LRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
Set rng = ws.Range("A2:J" & LRow)                   'Source Range to apply Filter on it
 
If Not ws.AutoFilterMode Then rng.AutoFilter        'Set AutoFilter if not already set
ws.AutoFilter.ShowAllData

rng.replace "&", " & "
rng.AutoFilter Field:=3, Criteria1:=critr1, Operator:=xlOr, Criteria2:=critr2

ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 1
 
End Sub