DigDB      Excel Add-ins      Add-in Tools for Excel Elites
Who else wants to do a 10-hour Excel analysis in 15 minutes? (more ...)

Features | Testimonial | Download free 15-day trial | Purchase | FAQ | Contact

Select cells in an Excel range by complex criteria ( wildcard )

Select cells by complex criteria from a range. This operation allows you to pinpoint all the cells that meet a certain condition. Once these cells are selected, you can format, highlight, clear, or set selected cells to other values.

This is different from filtering, because filtering works by tables' rows. Selecting cells simply select the cells based on the criteria you specify.

Select Cells allows you to select cells by these criteria:

  1. Select cells by wildcard match - allows '*', '?', match the text values of all cells
  2. Select cells by multiple complex conditions - for example, > 5 and < 10, or < 2/5/2004 or > 10/5/2004, or nested conditions like (A or B) and (C or D)
  3. Select cells that are blank - find not only the empty cells, but also those with only spaces, for example, a cell with value ' '.
  4. Select cell(s) that is the Maximum or Minimum value in an area, or in each row or column of an area
  5. Select cells by data types, i.e., Numbers, Dates, Texts, Errors, or Logical values
  6. Select cells that have longest or shortest texts
After the cells are selected, you can also expand selection to include the entire row or column for further data manipulation.

Select cells by wildcard match

  1. Select an area or several areas in a sheet first. If you don't select an area, then DigDB will auto-select the current neighboring region.
  2. Invoke 'Selected Area->Select Cells->Like (Wildcard)...' .

    Select cells by multiple criteria, wildcard

  3. Specify the criteria - suppose we want any cell that has 'oo' in it, then the criteria is '*oo*'

    Select cells by multiple criteria, wildcard

    Click 'Or Like' button to set multiple wildcard match criteria.

  4. Click 'OK' to select. Matched cells from the originally selected ranges are now selected.

    Select cells by multiple criteria, wildcard

More wildcard criteria options:

Multiple wildcard match criteria

    Click 'Or Like' button to set up multiple wildcard criteria, for example:

    Select cells by multiple criteria, wildcard

    Result is:

    Select cells by multiple criteria, wildcard

Select cells by multiple complex conditions

  1. Select an area or several areas in a sheet first. If you don't select an area, then DigDB will auto-select the current neighboring region.
  2. Invoke 'Selected Area->Select Cells->Like (Wildcard)...' .

    Select cells by multiple criteria, wildcard

  3. Specify the criteria

    Select cells by multiple criteria, wildcard

    Use the 'Add Criteria' to add more criteria

    Select cells by multiple criteria, wildcard

  4. Click 'OK' to select. Matched cells from the originally selected ranges are now selected.

    Select cells by multiple criteria, wildcard

    You can use 'Add Criteria' to add as many conditions as you want, but the multiple conditions are evaluated sequentially. Therefore, A or B and C or D is evaluated as ((A or B) and C) or D.

    Then, how to select (A or B) AND (C or D)? You can simply select A or B first, then select again C or D. The 2nd select works within the result of the first select.

Select Blank Cells - empty cells or cells with only spaces

  1. Select an area or several areas in a sheet first. If you don't select an area, then DigDB will auto-select the current neighboring region.
  2. Invoke 'Selected Area->Select Cells->Blank' .

    Select cells by multiple criteria, wildcard

  3. Cells that are empty and cells that are not empty but only have spaces or tabs in it.

    Select cells by multiple criteria, wildcard

Select cell(s) that is the Maximum or Minimum value in an area, or in each row or column of an area

  1. Select an area in a sheet first. If you don't select an area, then DigDB will auto-select the current neighboring region.
  2. Invoke 'Selected Area->Select Cells->Max->Numbers->...', you have several options

    Select cells by multiple criteria, wildcard

  3. 'Selected Area->Select Cells->Max->Numbers->Of All' = select the cells that has the Max number value in the selected range,

    Select cells by multiple criteria, wildcard

  4. 'Selected Area->Select Cells->Max->Numbers->by Row' = select the cells that has the Max number value in each row of the selected range

    Select cells by multiple criteria, wildcard

  5. 'Selected Area->Select Cells->Max->Numbers->by Column' = select the cells that has the Max number value in each column of the selected range

    Select cells by multiple criteria, wildcard

    Same with Dates and Minimum values.

Select cells by data types, i.e., Numbers, Dates, Texts, Errors, or Logical values

  1. Select an area in a sheet first. If you don't select an area, then DigDB will auto-select the current neighboring region.
  2. Invoke 'Selected Area->Select Cells->Types->...', you have several options

    Select cells by multiple criteria, wildcard

  3. 'Selected Area->Select Cells->Types->Number' = select the cells that are numbers from the selected range,

    Select cells by multiple criteria, wildcard

  4. 'Selected Area->Select Cells->Types->Date' = select the cells that are dates from the selected range

    Select cells by multiple criteria, wildcard

  5. 'Selected Area->Select Cells->Types->Text' = select the cells that are texts from the selected range. Note that empty cells are not considered texts. The 2 selected cells that look like empty have ' ' (spaces) in it to make it non-empty and thus text.

    Select cells by multiple criteria, wildcard

  6. 'Selected Area->Select Cells->Types->Error' = select the cells that are errors from the selected range,

    Select cells by multiple criteria, wildcard

  7. 'Selected Area->Select Cells->Types->Logical' = select the cells that are logical values ('TRUE' or 'FALSE') from the selected range

    Select cells by multiple criteria, wildcard

Select cells that have longest or shortest texts

  1. Select an area in a sheet first. If you don't select an area, then DigDB will auto-select the current neighboring region.
  2. Invoke 'Selected Area->Select Cells->Max->Length'

    Select cells by multiple criteria, wildcard

  3. Select Cells->Max->Length = select the cells having the maximum number of characters in shown texts

    Select cells by multiple criteria, wildcard

  4. Select Cells->Min->Length = select the cells having the minimum number of characters in shown texts

    Select cells by multiple criteria, wildcard

Need more?


Features | Testimonial | Download | Purchase | FAQ | Contact (free forum)

Copyright © 1999-2007 All rights reserved

DigDB      Excel Add-ins      Add-in Tools for Excel Elites
Who else wants to do a 10-hour Excel analysis in 15 minutes?

Related keywords: Advanced excel tips, excel questions tools, Excel reporting applications, Excel add-ins add-in, Excel shareware, Excel plugin plug-ins plugins, Excel tricks question, Excel utility utilities, Excel add-on add-ons, Excel tip tips, Power excel technique, Excel manipluation, Excel guru, Excel trick, Excel toolkit, Excel app power user

Microsoft Office Marketplace logo
Microsoft and the Office logo are trademarks or registered trademarks of
Microsoft Corporation in the United States and/or other countries.