Show Drop Down List With Specific Letters

If you've got a long list of items, it can take a while to find what you're looking for, in a data validation drop down list.

For example, in the screen shot below, you'd have to scroll down to the bottom of this long list, if you want to select the Teatime Chocolate Biscuits (and don't pick the Tofu by mistake!)


Type the First Letters

I've previously posted a few variations on a combo box technique, that lets you find an item based on the first few letter that you type.


But that trick won't help if you don't know the exact name. Maybe you know that there are chocolate biscuits in the list, but not the brand name.

Type a Few Letters

To help you out in that situation, I've uploaded a new sample file. In this workbook, there is a data validation drop down in column C, with a list of product names. If you type one or more letters in column B, the drop down list in that row will only show the items that contain that string of letters.


If you leave the cell in column B empty, the full list of items appears in the drop down.


If no products have the string of letters, the drop down won't work.


Use Wildcards in the Text String

To give the searches a bit more flexibility, you can use wildcards in the text strings, in column B. For example, type "a*x", to find any product that contain the letter "a", then any number of characters, followed by the letter "x".


Or use the "?" wildcard, to represent a single character.


Advanced Filter Programming

To create the short list, an Advanced Filter extracts products from the main list, based on the criteria that you typed in column B. The code runs automatically, when you click on a cell with a drop down list.

You can visit my Contextures website, for more details on Advanced Filter basics, and how to create complex criteria for an Advanced Filter.

Download the Sample File

To see how this technique works, you can download the sample file from my Contextures website. On the Sample Files page, go to the Data Validation section, and look for DV0066 – Data Validation – Contains Text

The zipped file is in xlsm format, and contains macros. Enable the macros, when you open the file, if you want to test the drop downs and search strings. To see the code, right-click on the DataEntry sheet tab then click View Code.


You may also like...

10 Responses

  1. peter says:


    DV0066 – Data Validation – Contains Text

    can’t see macro module??
    If hidden, how?


  2. Chris says:

    Hi I can’t see the macros eiher

  3. Louis says:

    Agree! Details on the Macro will help to develop a new one following the path.
    Can it be used as a template to build a new one?

  4. Martin Canosa says:

    Hi Debra !!

    what about using wildards in a combobox? I’m using a dropdown with 3 categories: id, name, and SSN. the selections indicates the field to look from to a second dropdown, populated with the result of the first selector.

    If I select, for instance, Name, then I’d like to type in SMITH, and that the result being smith, smithson, graysmith. just the ones containing the string, which in SQL would look like Select Name from Table1 where Name like ‘*smith*’.

    any ideas?

    Thanks !!


  5. Luis says:

    thanks for sharing your knowldege!! Would you mind helping me operate your macro datavalidationcontains.xlsm?
    when i type the letters in col B and press the drop down arrow, I get the standard X stop error message “value you entered is not valid. A user has restricted etc., retry cancel and so on.

    Any idea what’s preventing it from running normally?



  6. Luis says:

    Hi again,

    Please disregard my earlier post – just discovered what i was doing wrong duh :)

  7. Med says:

    I get a list made of numerical , and alpha numerical data type. the function seems consitatn however when I get simple number such as “2” ;”76″ the filtering seems not to work on this data type. Any reason why ?

Leave a Reply to Debra Dalgleish Cancel reply

Your email address will not be published. Required fields are marked *