VBA筛选Excel ID：匹配第一个下划线前与第二个下划线后字符

针对你的ID筛选问题，核心是要精准定位第一个下划线前的前缀和第二个下划线后的后缀，而非简单的包含匹配。下面给你几个实用的VBA实现思路：

方法一：用Split函数分割字符串（最直观） #

你的ID是固定的三段格式（两个下划线分隔），直接用Split函数把ID拆分成数组，然后判断数组第1段（前缀）和第3段（后缀）是否符合要求：

Sub FilterByPrefixAndSuffix()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim idArr As Variant
    Dim targetPrefix As String
    Dim targetSuffix As String
    
    ' 设置目标前缀和后缀（按需修改）
    targetPrefix = "C"
    targetSuffix = "2"
    
    Set ws = ThisWorkbook.Worksheets("Sheet1") ' 替换为你的工作表名
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row ' 假设ID在A列
    
    ' 遍历行筛选（示例：在B列标记符合条件的行）
    For i = 2 To lastRow ' 假设第1行是表头
        idArr = Split(ws.Cells(i, "A").Value, "_")
        ' 确保ID是三段格式，再判断前缀后缀
        If UBound(idArr) = 2 Then
            If idArr(0) = targetPrefix And idArr(2) = targetSuffix Then
                ws.Cells(i, "B").Value = "符合条件"
            End If
        End If
    Next i
End Sub

方法二：用InStr和InStrRev定位下划线位置 #

如果担心Split函数处理异常（比如ID格式不规范），可以用字符串定位函数精准截取前缀和后缀：

Sub FilterByPosition()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim idStr As String
    Dim firstUnderscore As Integer
    Dim lastUnderscore As Integer
    Dim prefix As String
    Dim suffix As String
    Dim targetPrefix As String
    Dim targetSuffix As String
    
    targetPrefix = "C"
    targetSuffix = "2"
    
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    For i = 2 To lastRow
        idStr = ws.Cells(i, "A").Value
        firstUnderscore = InStr(idStr, "_")
        lastUnderscore = InStrRev(idStr, "_")
        
        ' 确保有两个下划线且位置合法
        If firstUnderscore > 0 And lastUnderscore > firstUnderscore Then
            prefix = Left(idStr, firstUnderscore - 1)
            suffix = Mid(idStr, lastUnderscore + 1)
            If prefix = targetPrefix And suffix = targetSuffix Then
                ws.Cells(i, "B").Value = "符合条件"
            End If
        End If
    Next i
End Sub

方法三：用正则表达式（适配复杂格式） #

如果ID格式有更多变化（比如前缀是多字符、后缀带字母），正则表达式能更灵活匹配：

Sub FilterByRegex()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim idStr As String
    Dim regEx As Object
    Dim pattern As String
    Dim targetPrefix As String
    Dim targetSuffix As String
    
    targetPrefix = "C"
    targetSuffix = "2"
    ' 构建正则模式：^前缀_任意内容_后缀$（^和$确保整个字符串完全匹配）
    pattern = "^" & targetPrefix & "_.*_" & targetSuffix & "$"
    
    Set regEx = CreateObject("VBScript.RegExp")
    regEx.Global = False
    regEx.IgnoreCase = False ' 区分大小写，不需要则改成True
    
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    For i = 2 To lastRow
        idStr = ws.Cells(i, "A").Value
        If regEx.Test(idStr) Then
            ws.Cells(i, "B").Value = "符合条件"
        End If
    Next i
End Sub

额外小技巧：用Excel公式辅助筛选 #

如果不想写VBA，可在辅助列用公式提取前缀和后缀，再用自动筛选：

• 提取前缀：=LEFT(A2,FIND("_",A2)-1)
• 提取后缀：=RIGHT(A2,LEN(A2)-FIND("@",SUBSTITUTE(A2,"_","@",LEN(A2)-LEN(SUBSTITUTE(A2,"_","")))))
  之后筛选辅助列等于目标前缀、后缀的行即可。

内容的提问来源于stack exchange，提问作者bunnap