如何在Excel中针对单词列表搜索列表以过滤掉

我想搜索一个列表以过滤出其中是否包含某些单词。

我找到了这个VBA脚本

Sub GetWords()
Dim wrdLRow As Integer
Dim wrdLp As Integer
Dim CommentLrow As Integer
Dim CommentLp As Integer
Dim fndWord As Integer
Dim Sht As Worksheet
On Error Resume Next 'Suppress Errors... for when we don't find a match
'Define worksheet that has data on it....
Set Sht = Sheets("Sheet1")
'Get last row for words based on column A
wrdLRow = Sht.Cells(Rows.Count,"A").End(xlUp).Row
'Get last row for comments based on column C
CommentLrow = Sht.Cells(Rows.Count,"C").End(xlUp).Row
'Loop through lists and find matches....
For CommentLp = 2 To CommentLrow
For wrdLp = 2 To wrdLRow
'Look for word...
fndWord = Application.WorksheetFunction.Search(Sht.Cells(wrdLp,"A"),Sht.Cells(CommentLp,"C"))
'If we found the word....then
If fndWord > 0 Then
Sht.Cells(CommentLp,"D") = Sht.Cells(CommentLp,"D") & "; " & Sht.Cells(wrdLp,"A")
fndWord = 0 'Reset Variable for next loop
End If
Next wrdLp
Sht.Cells(CommentLp,"D") = Mid(Sht.Cells(CommentLp,"D"),3,Len(Sht.Cells(CommentLp,"D")) - 2)
Next CommentLp
End Sub


图片之前

图片后

它不仅搜索单词，还搜索单词中的字符。

如果C列中的字符串是“我如何使excel在单词列表中搜索单词？”

在A栏寻找的单词是“ how，ke，cel，abcd，xyz”

然后将返回“ how; ke; cel;”。

我只想要“如何”而不想要字符“ ke; cel;” “ make; excel”一词内。

这可以通过修改此脚本来完成，因此，如果该单词之前或之后包含空格，则它仅返回单词，因此可以将其猜测为整个单词而不是字符。

您可以使用InStr function。请记住，InStr function区分大小写。

以下仅是示例。修改和使用：

Option Explicit
Sub test()
Dim arr_For As Variant,arr_In As Variant
Dim i As Long,j As Long,Position As Long
Dim Valid As Boolean
With ThisWorkbook.Worksheets("Sheet1")
arr_For = .Range("A1:A5")
arr_In = .Range("B1:B5")
For i = LBound(arr_For) To UBound(arr_For)
For j = LBound(arr_In) To UBound(arr_In)
Position = InStr(arr_In(j,1),arr_For(i,1))
If Position > 0 Then
Valid = False
If Position = 1 And Mid(arr_In(j,Position + Len(arr_For(i,1)),1) = " " Then
Valid = True
ElseIf Position > 1 Then
If Position + Len(arr_For(i,1)) = Len(arr_In(j,1)) + 1 And Mid(arr_In(j,Position - 1,1) = " " Then
Valid = True
ElseIf Mid(arr_In(j,1) = " " And Mid(arr_In(j,1) = " " Then
Valid = True
End If
End If
If Valid = True Then
If .Cells(j,3).Value = "" Then
.Cells(j,3).Value = arr_For(i,1)
Else
.Cells(j,3).Value = .Cells(j,3).Value & " ; " & arr_For(i,1)
End If
End If
End If
Next j
Next i
End With
End Sub

,

您可以查看一下并对其进行一些编辑：

=IF(IFERROR(FIND(B$2,$A3,0),1,"")


您可以通过将“，1”更改为“，B $ 2”来返回字母，如果可以考虑使用非vba解决方案，则可以考虑将多个字母串联在一起。

编辑，仅显示其灵活性：

尝试以下操作：

_{样本数据：}

_{示例代码：}

Sub GetWords()
Dim lr1 As Long,lr2 As Long,x As Long
Dim arr1 As Variant,arr2 As Variant
Dim hits As String
With Sheet1 'Change according to your sheets CodeName
'Get list of words to check
lr1 = .Cells(.Rows.Count,1).End(xlUp).Row
arr1 = .Range("A2:A" & lr1).Value
'Get list of sentences to check
lr2 = .Cells(.Rows.Count,3).End(xlUp).Row
arr2 = .Range("C2:D" & lr2).Value
'Loop through all sentences in memory
For x = LBound(arr2) To UBound(arr2)
For Each itm In arr1
If IsNumeric(Application.Match(itm,Split(arr2(x," "),0)) Then
If hits <> "" Then
hits = hits & "; " & itm
Else
hits = itm
End If
End If
Next itm
arr2(x,2) = hits
hits = ""
Next x
'Load hits into column D
.Range("C2:D" & lr2).Value = arr2
End With
End Sub


_{样本结果：}

它已经扩展了一些，但我只是想逐步讲解它，并对发生的事情进行一些解释。