在Excel中查找多个文本 [英] Finding Multiple text in Excel

问题描述

我在excel中有非常大量的数据，我想摆脱包含我的标签的行。查找选项允许我只有一个单词/搜索，我有数百个单词。它可能是一个宏观或争论。我尝试了以下参数，但它只发现1.

  = IF（OR（ISNUMBER（SEARCH（{word1 word2，A1，B1）））YES，NO）
  

解决方案

之前

宏运行

  Option Explicit 
 
 Sub RemoveRowsBasedOnArrayCondition（）
 Dim searchTerms As Variant 
 searchTerms = Array（ tag1，tag2，tag3）
 
 ReDim rowsToDelete（0）As String 
 
 Dim allRange As Range 
设置allRange = ActiveSheet.UsedRange 
 
 Dim cell As Range，word As Variant 
对于每个单元格在allRange 
对于每个单词在searchTerms 
如果InStr（1，单元格，单词，vbTextCompare）然后
 rowsToDelete（UBound（rowsToDelete））= CStr（cell.Row）
 ReDim Prese rve rowsToDelete（UBound（rowsToDelete）+ 1）
结束If 
下一个单词
下一个单元格
 ReDim保留rowsToDelete（UBound（rowsToDelete） -  1）
 RemoveDuplicate rowsToDelete 
 Dim v As Long 
对于v = UBound（rowsToDelete）到LBound（rowsToDelete）步骤-1 
行（rowsToDelete（v））。删除
下一个
 End Sub 
 
 
 Sub RemoveDuplicate（ByRef StringArray（）As String）
 Dim lowBound $，UpBound& A& B& cur& tempArray（）As String 
 If（Not StringArray）= True Then Exit Sub 
 lowBound = LBound（StringArray）：UpBound = UBound（StringArray）
 ReDim tempArray（lowBound To UpBound）
 cur = lowBound：tempArray ）= StringArray（lowBound）
对于A = lowBound + 1 To UpBound 
对于B = lowBound To cur 
如果LenB（tempArray（B））= LenB（StringArray（A））Then 
如果InStrB（1，StringArray（A），tempArray（B），vbBinaryCompare）= 1然后退出Fo r 
结束如果
下一个B 
如果B> cur then cur = B：tempArray（cur）= StringArray（A）
下一个A 
 ReDim保存tempArray（lowBound To cur）：StringArray = tempArray 
 End Sub 
  

之后

I have a very large amount of data in excel and I want to get rid of rows which contain my "tags". The Find option allows me just 1 word/search and I have hundreds of words. Its probably a macro or an argument. I tried the following argument but its only found 1.

=IF(OR(ISNUMBER(SEARCH({"word1","word2","word3"},A1,B1)))"YES","NO")

Someone help me pls.

解决方案

before

macro to run

Option Explicit

Sub RemoveRowsBasedOnArrayCondition()
    Dim searchTerms As Variant
    searchTerms = Array("tag1", "tag2", "tag3")

    ReDim rowsToDelete(0) As String

    Dim allRange As Range
    Set allRange = ActiveSheet.UsedRange

    Dim cell As Range, word As Variant
    For Each cell In allRange
        For Each word In searchTerms
            If InStr(1, cell, word, vbTextCompare) Then
                rowsToDelete(UBound(rowsToDelete)) = CStr(cell.Row)
                ReDim Preserve rowsToDelete(UBound(rowsToDelete) + 1)
            End If
        Next word
    Next cell
    ReDim Preserve rowsToDelete(UBound(rowsToDelete) - 1)
    RemoveDuplicate rowsToDelete
    Dim v As Long
    For v = UBound(rowsToDelete) To LBound(rowsToDelete) Step -1
        Rows(rowsToDelete(v)).Delete
    Next
End Sub


Sub RemoveDuplicate(ByRef StringArray() As String)
    Dim lowBound$, UpBound&, A&, B&, cur&, tempArray() As String
    If (Not StringArray) = True Then Exit Sub
    lowBound = LBound(StringArray): UpBound = UBound(StringArray)
    ReDim tempArray(lowBound To UpBound)
    cur = lowBound: tempArray(cur) = StringArray(lowBound)
    For A = lowBound + 1 To UpBound
        For B = lowBound To cur
            If LenB(tempArray(B)) = LenB(StringArray(A)) Then
                If InStrB(1, StringArray(A), tempArray(B), vbBinaryCompare) = 1 Then Exit For
            End If
        Next B
        If B > cur Then cur = B: tempArray(cur) = StringArray(A)
    Next A
    ReDim Preserve tempArray(lowBound To cur): StringArray = tempArray
End Sub

and after

这篇关于在Excel中查找多个文本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！