How to find more than one word in Excel?

M

man

Board Regular
Joined
Jul 26, 2010
Messages
78
Office Version
  1. 2021
Platform
  1. Windows
Hello

For example, I want to find cells that contains these 2 words [disposable] [bottles] in any order or with any other words in between, the search result shown will be A4 cell and A5 cell. How to do it? (Unable to use Ctrl+F because Ctrl+F only can search for 1 word)

My A1 cell to A6 cell contents are below
A1 cell: A water bottle is a container that is used to hold liquids, mainly water, for the purpose of transporting a drink while travelling or while otherwise away from a supply of potable water.
A2 cell: Water bottles are usually made of plastic, glass, metal, or some combination of those substances.
A3 cell: in the past, water bottles were sometimes made of wood, bark, or animal skins such as leather, hide and sheepskin.
A4 cell: Water bottles can be either disposable or reusable.
A5 cell: Disposable water bottles are often sold filled with potable water, while reusable bottles are often sold empty.
A6 cell: Reusable water bottles help cut down on consumer plastic waste and carbon emissions.

I am using Excel 2021

Thanks
 
Sort by date Sort by votes
When you hit debug, hover your mouse over ws.Name on this line. What does it say? Is it one of the sheets in your workbook? Can't see your workbook so don't know what you're doing wrong. Post the screen shot of your code when it's yellow.
VBA Code: 
  If ws.Name <> "Output" Then

It doesn't matter what sheet you changed it from as long as there's a sheet called Output.
 
Upvote 0

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Cubist said:
Is one of your sheets empty? If it is then there's nothing to find. This should handle when that happens.

VBA Code: 
Sub Extract()
    Dim a, b() As Variant
    Dim i As Long, j As Long, k As Long
    Dim ws As Worksheet
    Dim ws2 As Worksheet
 
    ' Change the name of Sheet2 to "Output"
    Set ws2 = ThisWorkbook.Worksheets("Output") ' Output sheet
 
    ' Clear previous data in the output sheet
    ws2.Cells.Clear
    k = 1
    ReDim b(1 To 2 ^ 20, 1 To 1)
 
    ' Loop through each worksheet that is not named "Output"
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Output" Then
              a = ws.UsedRange.Value
            ' Check if the worksheet has any used range
            If Not IsEmpty(a) Then
                ' Count the number of matching elements
                For i = 1 To UBound(a, 1)
                    For j = 1 To UBound(a, 2)
                        ' Add words here as needed with similar syntax
                        On Error Resume Next
                        If InStr(1, a(i, j), "Sold", vbTextCompare) > 0 And _
                           InStr(1, a(i, j), "Disposable", vbTextCompare) > 0 And _
                           InStr(1, a(i, j), "Bottle", vbTextCompare) > 0 Then
                           b(k, 1) = a(i, j)
                           k = k + 1
                        End If
                        On Error GoTo 0
                    Next j
                Next i
            End If
        End If
    Next ws
 
    ' Output the results to the "Output" sheet
    If k > 0 Then
        ws2.Range("A1").Resize(k, 1).Value = b
    End If
End Sub
Click to expand...
I close the excel sheet then reopen the excel sheet and redo everything and it is working fine now. No error. Output sheet can show the results. Thanks.

How to know each cell inside Output sheet is copied from what sheet?

How to save everything, including the VB editor and the Output sheet? This screenshot appear after I click save button. Even if I close the VB editor (Alt + F11. This will bring up the VB editor.) and delete the Output sheet, this screenshot will also appear after I click save button.
the following features cannot be saved.PNG
 
Upvote 0
You need to Save As a macro-enabled or binary workbook since you're using VBA now. This will output the sheet name in column B.
VBA Code: 
Sub Extract()
    Dim a, b() As Variant
    Dim i As Long, j As Long, k As Long
    Dim ws As Worksheet
    Dim ws2 As Worksheet
   
    ' Change the name of Sheet2 to "Output"
    Set ws2 = ThisWorkbook.Worksheets("Output") ' Output sheet
   
    ' Clear previous data in the output sheet
    ws2.Cells.Clear
    k = 1
    ReDim b(1 To 2 ^ 20, 1 To 2)
    
    ' Loop through each worksheet that is not named "Output"
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Output" Then
              a = ws.UsedRange.Value
            ' Check if the worksheet has any used range
            If Not IsEmpty(a) Then
                ' Count the number of matching elements
                For i = 1 To UBound(a, 1)
                    For j = 1 To UBound(a, 2)
                        ' Add words here as needed with similar syntax
                        On Error Resume Next
                        If InStr(1, a(i, j), "Sold", vbTextCompare) > 0 And _
                           InStr(1, a(i, j), "Disposable", vbTextCompare) > 0 And _
                           InStr(1, a(i, j), "Bottle", vbTextCompare) > 0 Then
                           b(k, 1) = a(i, j)
                           b(k, 2) = ws.Name
                           k = k + 1
                        End If
                        On Error GoTo 0 ' Reset error handling
                    Next j
                Next i
            End If
        End If
    Next ws
   
    ' Output the results to the "Output" sheet
    If k > 0 Then
        ws2.Range("A1").Resize(k, 2).Value = b
    End If
End Sub
 
  • Like
Reactions: man
Upvote 0
Cubist said:
You need to Save As a macro-enabled or binary workbook since you're using VBA now. This will output the sheet name in column B.
VBA Code: 
Sub Extract()
    Dim a, b() As Variant
    Dim i As Long, j As Long, k As Long
    Dim ws As Worksheet
    Dim ws2 As Worksheet
  
    ' Change the name of Sheet2 to "Output"
    Set ws2 = ThisWorkbook.Worksheets("Output") ' Output sheet
  
    ' Clear previous data in the output sheet
    ws2.Cells.Clear
    k = 1
    ReDim b(1 To 2 ^ 20, 1 To 2)
   
    ' Loop through each worksheet that is not named "Output"
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Output" Then
              a = ws.UsedRange.Value
            ' Check if the worksheet has any used range
            If Not IsEmpty(a) Then
                ' Count the number of matching elements
                For i = 1 To UBound(a, 1)
                    For j = 1 To UBound(a, 2)
                        ' Add words here as needed with similar syntax
                        On Error Resume Next
                        If InStr(1, a(i, j), "Sold", vbTextCompare) > 0 And _
                           InStr(1, a(i, j), "Disposable", vbTextCompare) > 0 And _
                           InStr(1, a(i, j), "Bottle", vbTextCompare) > 0 Then
                           b(k, 1) = a(i, j)
                           b(k, 2) = ws.Name
                           k = k + 1
                        End If
                        On Error GoTo 0 ' Reset error handling
                    Next j
                Next i
            End If
        End If
    Next ws
  
    ' Output the results to the "Output" sheet
    If k > 0 Then
        ws2.Range("A1").Resize(k, 2).Value = b
    End If
End Sub
Click to expand...
The code works, can see sheet name in column B. Thanks.

How to Save As a macro-enabled or binary workbook? I click the [Save] button in this screenshot, not sure does this [Save] button do "Save As a macro-enabled or binary workbook"?
the following features cannot be saved.PNG


After clicking the [Save] button in the above screenshot, every time I open the excel sheet I need to redo the VB editor steps again, is it normal?
Cubist said:
Locate the "Developer" tab. If you don't see it then
  1. On the File tab, go to Options > Customize Ribbon.
  2. Under Customize the Ribbon and under Main Tabs, select the Developer checkbox

Once you have the developer tab,
  1. Alt + F11. This will bring up the VB editor.
  2. Insert -> Module -> Paste in the code.
  3. Press F5 to execute.
  4. Check the result.
Click to expand...
 
Upvote 0
Cubist said:
You need to Save As a macro-enabled or binary workbook since you're using VBA now. This will output the sheet name in column B.
VBA Code: 
Sub Extract()
    Dim a, b() As Variant
    Dim i As Long, j As Long, k As Long
    Dim ws As Worksheet
    Dim ws2 As Worksheet
 
    ' Change the name of Sheet2 to "Output"
    Set ws2 = ThisWorkbook.Worksheets("Output") ' Output sheet
 
    ' Clear previous data in the output sheet
    ws2.Cells.Clear
    k = 1
    ReDim b(1 To 2 ^ 20, 1 To 2)
 
    ' Loop through each worksheet that is not named "Output"
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Output" Then
              a = ws.UsedRange.Value
            ' Check if the worksheet has any used range
            If Not IsEmpty(a) Then
                ' Count the number of matching elements
                For i = 1 To UBound(a, 1)
                    For j = 1 To UBound(a, 2)
                        ' Add words here as needed with similar syntax
                        On Error Resume Next
                        If InStr(1, a(i, j), "Sold", vbTextCompare) > 0 And _
                           InStr(1, a(i, j), "Disposable", vbTextCompare) > 0 And _
                           InStr(1, a(i, j), "Bottle", vbTextCompare) > 0 Then
                           b(k, 1) = a(i, j)
                           b(k, 2) = ws.Name
                           k = k + 1
                        End If
                        On Error GoTo 0 ' Reset error handling
                    Next j
                Next i
            End If
        End If
    Next ws
 
    ' Output the results to the "Output" sheet
    If k > 0 Then
        ws2.Range("A1").Resize(k, 2).Value = b
    End If
End Sub
Click to expand...
If I want to search for more words (for example 4 words: Sold, Disposable, Bottle, Reusable), is it add one more row
VBA Code: 
InStr(1, a(i, j), "Reusable", vbTextCompare) > 0 And _
below this row
VBA Code: 
If InStr(1, a(i, j), "Sold", vbTextCompare) > 0 And _

-----

If I want to change the search word to other word, is it just replace the word Sold/Disposable/Bottle seen in the code to other word I want to search? Is the search word case sensitive?
 
Last edited:
Upvote 0
Yes, Save As macro-enabled or binary so it doesn't undo your code.
Yes, add a line for additional words.
No, They're not case-sensitive but when you're searching for plurals don't include the "s" e.g. "bottle" instead of "bottles". If you search the plural it won't return singular, but if you search singular it will return both singular and plural.
 
  • Like
Reactions: man
Upvote 0
Cubist said:
Yes, Save As macro-enabled or binary so it doesn't undo your code.
Yes, add a line for additional words.
No, They're not case-sensitive but when you're searching for plurals don't include the "s" e.g. "bottle" instead of "bottles". If you search the plural it won't return singular, but if you search singular it will return both singular and plural.
Click to expand...
How to Save As macro-enabled or binary so it doesn't undo my code, click where? Now every time I open the excel sheet I need to redo the VB editor steps again.

If I search for plural is it the result will show plural only? e.g. if I search for "bottles" will the result show "bottles"?
 
Upvote 0
F12 ->Save as type -> Pick Macro enabled or binary
Correct. It will not return "I sold a disposable water bottle".
 
  • Like
Reactions: man
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Code to use multiple trigger cells to hide / unhide rows of corresponding question sets
Replies
1
Views
1K
dmt32
D
Oakie_Uffda
  • Question Question
Split Cell into multiple without splitting words and print decent
Replies
1
Views
1K
Oakie_Uffda
Oakie_Uffda
W
  • Question Question
Subsitute more than one words in the columns
2
Replies
11
Views
749
hiker95
H
S
  • Question Question
Search bank data for more than one word in order to categorize expenditures
Replies
5
Views
802
shyy
S

Forum statistics

Threads
1,224,816
Messages
6,181,143
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top