DigDB           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 )

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

Practice file - select-cells-demo.xls (16k)

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

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)...' .

   

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

   

   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.

   

More wildcard criteria options:

Wildcard examples using '*' and '?' - '?' represents a single character in the same position as the question mark
• '*east' matches 'Northeast' or 'Southeast' or 'far east'
• '* east' matches 'far east', but NOT 'Northeast'
• '* e*t' matches 'far east' or 'go erupt'
• 'sm?th' matches 'smith' and 'smyth', but NOT 'smooth'
• '? apple' matches 'a apple', but NOT 'an apple'
• '??/??/??' matches dates like '02/03/04', but NOT '2/3/04'

How to wildcard match the cells that are Dates and Numbers?

• Wildcard match will match cells by the texts that are in the cell. It treats the cell as the text that is shown, regardless of the internal value of the cell. Therefore, a cell may have value 5.1234, but if it is formatted to show only 2 decimals, i.e., if it's shown as 5.12, then it matches to '*.*2'. Similiarly, in terms of value '2/7/04' is the same as '02/07/04' or 'Feb 07, 2004', but 'Feb*' will only match the cells shown as 'Feb 07,2004'

What if there is a '*' in the cells and I want to find it without wildcarding?

In my data there are '*' used in the cells. For example, 'E*trade', how do I find 'E*trade' without wildcard matches? - Use 'DigDB->Selected Area->Select Cells->Criteria...' command, where you can set exact match without wildcarding.

Multiple wildcard match criteria

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



Result is:

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)...' .

   

3. Specify the criteria

   

   Use the 'Add Criteria' to add more criteria

   

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

   

   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' .

   

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

   

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

   

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

   

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

   

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

   

   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

   

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

   

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

   

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.

   

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

   

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

   

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'

   

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

   

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

   

Need more?

• Browse the complete list of DigDB's powerful features listed on the front page.
• If you see errors or things not working, check out our error reference page.

---

Features | Testimonial | Download | Purchase | FAQ | Contact

Copyright © 1999-2007 All rights reserved

DigDB           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 and the Office logo are trademarks or registered trademarks of
Microsoft Corporation in the United States and/or other countries.