How to search multiple words in excel?


  1. Posts : 338
    Windows 10 Home 64 bit x64 Version 22H2
       #1

    How to search multiple words in excel?


    In excel 2021 there are many cells and I want to search for the cell that contains multiple keywords in no particular order, how to search?

    For example the excel sheet has A1 to A6 total 6 cells contents below.

    I want find the cell that contains these 2 words "reusable" and "plastic", the result shown to me will be A6 cell.

    I want find the cell that contains these 3 words "water" and "wood" and "leather", the result shown to me will be A3 cell.

    How to search/find in excel 2021?

    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.

    Thanks
      My Computer


  2. Posts : 16,958
    Windows 10 Home x64 Version 22H2 Build 19045.4170
       #2

    Excel Find is quite limited. You can use wildcards between the search terms but that's about it.
    Intel*update will find a cell containing Intel update but not one containing update Intel.
    But I do not know of any method of searching for more than two terms without having to specify them in sequence.
    failed*Intel*update will find a cell containing failed Intel update but not one containing Intel update failed.


    If you set up a Filter on the column you'd be able to use a Custom filter for it containing two [maximum two] search terms and their sequence will not matter.
    How to search multiple words in excel?-excel-filter-dropdown.png
    How to search multiple words in excel?-.png - How to search multiple words in excel?-.png
    Both of these would will find a cell containing Intel update and one containing update Intel.
    You can use wildcards in the filters but the sequence of those terms limits what you can achieve just as it does when using Find.

    You could look up Find or Filter in Excel Help and it would say the same.

    To get any further would need tailored Functions or even VBA. For example,
    Excel: Use Search Function to Search Multiple Values - Statology


    Denis
      My Computer


  3. Posts : 338
    Windows 10 Home 64 bit x64 Version 22H2
    Thread Starter
       #3

    @Try3 I click Data menu then I click the Filter button but I did not see the Custom AutoFilter box appear, what steps did you do to make the Custom AutoFilter box appear? I am using Excel 2021.
      My Computer


  4. Posts : 16,958
    Windows 10 Home x64 Version 22H2 Build 19045.4170
       #4

    Click on the dropdown arrow in the top row for the column concerned - it is shown when the Filter is on -
    How to search multiple words in excel?-excel-filter-use.png
    then click on Text filters followed by either Contains or Custom filter.
    How to search multiple words in excel?-choose-text-filters.png


    Excel Help also contains this information.


    Denis
      My Computer


  5. Posts : 338
    Windows 10 Home 64 bit x64 Version 22H2
    Thread Starter
       #5

    @Try3

    How to filter all columns instead of only one column?

    Do you need to tick the box beside (Select All) and tick all the bottom boxes? It seems like tick or untick the results are the same.

    Before you start the filter process, is it just select any single cell?

    "Excel Help also contains this information" What keyword do you search for my problem?

    Thanks
      My Computer


  6. Posts : 16,958
    Windows 10 Home x64 Version 22H2 Build 19045.4170
       #6

    Not possible.
    Neither - leave them alone. Click on Text filters followed by either Contains or Custom filter.
    You do not need to select anything.
    I've already suggested Find or Filter.


    Denis
      My Computer


  7. Posts : 338
    Windows 10 Home 64 bit x64 Version 22H2
    Thread Starter
       #7

    @Try3

    In Custom Autofilter I select [contains], input [disposable], select [And], select [contains], input [empty] then click OK, then excel only shows me A1 and A5 in the sheet. The result should be A5 only, why the result include A1?

    How to search multiple words in excel?-disposable-empty.png
      My Computer


  8. Posts : 16,958
    Windows 10 Home x64 Version 22H2 Build 19045.4170
       #8

    Filter only filters rows below the row it is in as it assumes you have put it in a header row.
    You've put your filter on your data instead of in a row of headers.
    It's not "including" A1, it is showing the cell the filter is in as though it was a header.
    So insert a new row 1, write some explanatory text as a header if you want, remove the filter, select the new row 1, set up the filter again.
    Just like my post #4.


    Denis
      My Computer


 

  Related Discussions
Our Sites
Site Links
About Us
Windows 10 Forums is an independent web site and has not been authorized, sponsored, or otherwise approved by Microsoft Corporation. "Windows 10" and related materials are trademarks of Microsoft Corp.

© Designer Media Ltd
All times are GMT -5. The time now is 17:21.
Find Us




Windows 10 Forums