如何批量将单元格内容中的关键字提取出来，有关键字列表

15098801898

看来都需用到instr，谢谢各位！

demonimissyou

vba自定义函数
Option Explicit
——————————————————————————————————
Function rule1(rng As Range) As String
    Dim rexp As RegExp
    Dim matches As Object
    Dim temp As Variant
    Set rexp = New RegExp
   
    rexp.Global = True
    rexp.Pattern = "美的|华帝|海尔|苏泊尔|格力|海信|美菱|九阳|帅康"
    Set matches = rexp.Execute(rng.Value)
   
    For Each temp In matches
        rule1 = temp.Value
    Next
   
    Set rexp = Nothing
End Function
——————————————————————————————————————————————————
Function rule2(rng As Range) As String
    Dim rexp As RegExp
    Dim matches As Object
    Dim temp As Variant
    Set rexp = New RegExp
   
    rexp.Global = True
    rexp.Pattern = "电热水器|燃气热水器|洗衣机|烟灶套装|净水器|冰箱|空调|饮水机"
    Set matches = rexp.Execute(rng.Value)
   
    For Each temp In matches
        rule2 = temp.Value
    Next
   
    Set rexp = Nothing
End Function

blackttea1

学习啦，谢谢！

gwjkkkkk

Option Explicit
Sub TEST1()
    Dim ar, br, i&, j&, strPat$, Matches As Object, aMatch As Object, dic As Object
   
    ar = Worksheets(2).[A1].CurrentRegion.Value
    For j = 1 To UBound(ar, 2)
        strPat = ""
        For i = 2 To UBound(ar)
            If Len(ar(i, j)) Then strPat = strPat & "|" & ar(i, j)
        Next i
        ar(1, j) = Mid(strPat, 2)
    Next j
            
    With [A1].CurrentRegion
        .Offset(1, 1).Clear
        br = .Value
    End With
            
    Set dic = CreateObject("Scripting.Dictionary")
    With CreateObject("VBScript.RegExp")
        .Global = True
        For j = 2 To 3
            .Pattern = ar(1, j - 1)
            For i = 2 To UBound(br)
                If .test(br(i, 1)) Then
                    dic.RemoveAll
                    Set Matches = .Execute(br(i, 1))
                    For Each aMatch In Matches
                        dic(aMatch.Value) = Empty
                    Next
                    br(i, j) = Join(dic.keys, ",")
                End If
            Next i
        Next j
    End With
   
    [A1].CurrentRegion.Value = br
    Set Matches = Nothing: Set dic = Nothing
    Beep
End Sub

gwjkkkkk

参与一下。。。

wang-way

我也来凑个热闹啊

1. Sub GetdataWithRegex()
   
2.     Dim wb As Workbook, sht As Worksheet
   
3.     Set wb = Application.ThisWorkbook
   
4.     Set sht = wb.Worksheets("规则")
   
5.     With sht
   
6.         '根据规则表构建正则表达式
   
7.         s = ""
   
8.         '品牌
   
9.         j = 1
   
10.         p = ""
    
11.         erow = .Cells(.Rows.Count, j).End(xlUp).Row
    
12.         For i = 2 To erow
    
13.             If p = "" Then
    
14.                 p = .Cells(i, j).Value
    
15.             Else
    
16.                 p = p & "|" & .Cells(i, j).Value
    
17.             End If
    
18.         Next i
    
19.         '产品
    
20.         j = 2
    
21.         p2 = ""
    
22.         erow = .Cells(.Rows.Count, j).End(xlUp).Row
    
23.         For i = 2 To erow
    
24.             If p2 = "" Then
    
25.                 p2 = .Cells(i, j).Value
    
26.             Else
    
27.                 p2 = p2 & "|" & .Cells(i, j).Value
    
28.             End If
    
29.         Next i
    
30.     End With
    
31.     Debug.Print p, p2
    
32.     '创建正则对象
    
33.     Set reg = CreateObject("VBScript.RegExp")
    
34.     reg.Pattern = p
    
35.     Set reg2 = CreateObject("VBScript.RegExp")
    
36.     reg2.Pattern = p2
    
37.     Set sht = wb.Worksheets("需提取数据")
    
38.     With sht
    
39.         erow = .Cells(.Rows.Count, 1).End(xlUp).Row
    
40.         For i = 2 To erow
    
41.             If reg.test(.Cells(i, 1).Value) Then .Cells(i, 2).Value = reg.Execute(.Cells(i, 1).Value)(0)
    
42.             If reg2.test(.Cells(i, 1).Value) Then .Cells(i, 3).Value = reg2.Execute(.Cells(i, 1).Value)(0)
    
43.         Next i
    
44.     End With
    
45. End Sub

复制代码

我的城市你来过

=BYCOL(IFNA(REGEXP(A2,HSTACK($D$2:$D$10,$E$2:$E$10)),""),CONCAT)

zhangcheng6688

正则表达式，MARK

mjm04

WPS正则

B2单元格：
=IFERROR(TOCOL(REGEXP(A2,规则!$A$2:$A$10),3),"")

C2单元格：
=IFERROR(TOCOL(REGEXP(A2,规则!$B$2:$B$9),3),"")

zc123zc

直接instr一用，简单又明了