PowerGREP

Figure 14-23

Exercises

1.In the first example in this chapter, the character sequence regexp was never matched, although the character sequence regexp does occur in the sample file Regex.txt, and the pattern regexp is one of the options in each of the patterns tested. Provide an improved pattern that will match all occurrences of the character sequence regexp.

2.Create a pattern to match dollar values with two digits before the decimal point and two after it. A sample value is $88.23.

349

15

Wildcards in Microsoft Excel

Microsoft Office Excel is one of the most successful applications in the Microsoft Office suite. Much of the data held in Excel worksheets is text that is searchable. Excel provides a search facility on formulas, values, or comments in a worksheet or workbook.

Excel does not have full regular expression support. Like Microsoft Word, Excel has limited support for regular expressions, by means of wildcards. The range of wildcards in Excel is significantly more limited than in Word, but as you’ll see, the wildcard functionality that is provided can, when used together with other Excel tools, provide useful productivity gains.

In this chapter, you will learn the following:

The interface to Excel wildcard functionality in the Find and Replace dialog box

The wildcards that Excel supports

How to use those wildcards in searches

How to use wildcards in data forms

How to use wildcards in filters

The wildcard functionality described in this chapter was tested on Microsoft Office Excel 2003.

The Excel Find Interface

The Excel interface for using wildcards has similarities to the interface in Microsoft Word. The Find and Replace dialog box, which is shown in Figure 15-1, is central to the process.

Chapter 15

Figure 15-1

A sample spreadsheet, Months.xls, will be used to explore how the Find and Replace dialog box works. The appearance of Months.xls is shown in Figure 15-2. Notice that it contains simple text values in several cells and a sum in cell B15.

Figure 15-2

Try It Out

The Find and Replace Interface

1.Open the worksheet Months.xls in Excel, and use the Ctrl+F keyboard shortcut to open the Find and Replace dialog box.

2.In the Find What text box, type the character sequence Jan; click the Find Next button; and inspect the results, as shown in Figure 15-3. The cell A2, which contains the literal character sequence Jan, is highlighted.

3.The Find and Replace dialog box in Excel has a Find All button, a feature that is absent from Microsoft Word. Click the Find All button, and inspect the results, as shown in Figure 15-4. Notice that a list of matches is displayed in the lower part of the Find and Replace dialog box. Only one match is highlighted: the first cell in the search order, which in this case is again cell A2.

352

Wildcards in Microsoft Excel

Figure 15-3

The reason for the list displayed in the lower part of the Find and Replace dialog box is that some matches may not be on the currently displayed screen. Particularly in large spreadsheets, there may be multiple matches, likely spread over several screens of information.

Figure 15-4

353

Chapter 15

The list of matches displayed in the bottom of the Find and Replace dialog box allows you to easily navigate to the match of interest, whether or not it is on-screen. In this simple example, there are only two matches.

4.Click the lower match in the list in the lower part of the Find and Replace dialog box. Figure 15-5 shows the appearance after this step. Notice that the cell D2 is now highlighted because it, too, contains the character sequence Jan.

Figure 15-5

The Find and Replace dialog box has a Match Case check box that does what you would expect. If checked, it converts the default case-insensitive matching to case-sensitive matching.

The Match Entire Cell Contents check box, if checked, means that the literal pattern Jan will match only if Jan is the whole content of a cell.

5.Check the Match Entire Cell Contents check box; click the Find All button; and inspect the results, as shown in Figure 15-6. Notice that now only one match is listed. Cell D2 is no longer included in the list of matches because Jan is only a part of that cell’s content.

Before looking at the effect of the Within, Search, and Look In drop-down lists, look at the limited range of regular expression–like functionality provided by Excel wildcards.

354

Wildcards in Microsoft Excel

Figure 15-6

The Wildcards Excel Suppor ts

Excel supports fewer wildcards than any other tool described in this book. It supports only three metacharacters, which are listed in the following table.

Metacharacter	Meaning
?	Matches any single character
*	Matches any sequence of 0 or more Characters
~	The escape character

Try It Out		The Excel Wildcards
1.	Open Months.xls in Excel. Ensure that the Match Entire Cell Contents check box is unchecked.
2.	In the Find box, type the pattern J?n. The pattern will match character sequences such as Jan
	and Jun, each of which occurs twice in Months.xls.
3.	Click the Find All button, and inspect the results displayed in the lower part of the Find and
	Replace dialog box, as shown in Figure 15-7.

Clicking any of the items listed in the lower part of the Find and Replace dialog box allows you to navigate to any desired value, whether it is currently visible on-screen or not.

4. Click the third line, whose value is Jan Sales. Figure 15-8 shows that the chosen cell is now

highlighted.	355

Chapter 15

Figure 15-7

Figure 15-8

356

Wildcards in Microsoft Excel

5.Click other matches in the lower part of the Find and Replace dialog box to confirm that you navigate to the chosen cell.

6.Edit the pattern in the Find What text box to A*; click the Find All button; and inspect the results, as shown in Figure 15-9. Notice that every occurrence of the alphabetic character A or a is matched.

The number of matches for the pattern A* is unacceptably large even for the small amount of data in Months.xls. Excel has no notion of positional metacharacters such as the beginning- of-line metacharacter, ^, so you can’t narrow down the matches using that technique. In this example, you can remove the undesired matches due to the word sales by specifying a casesensitive match. In Excel, the default is a case-insensitive match.

7.Check the Match Case check box; click the Find All button; and inspect the results, as shown in Figure 15-10. Notice that now only matches that have an uppercase A are in the cell.

This example illustrates a general problem with matching using the limited wildcards in Excel, which is that specificity can sometimes be very low.

Don’t close Excel, because you will continue from this point in the next example.

Figure 15-9

357

Chapter 15

Figure 15-10

How It Works

The pattern J?n matches any character sequence that begins with a J and is followed by any character which, in turn, is followed by an n. In the sample data, the character sequences Jan and Jun match.

The pattern A*, when the Match Case check box is unchecked, will match any character sequence that contains A or a followed by any number of characters. The biggest source of undesired matches is the word sales, which occurs 12 times. That matches because it contains an a that isn’t the final character of the word, and the matching is being carried out case insensitively.

In large worksheets the number of matches could be overwhelming if you chose a pattern as nonspecific as A*. Another technique to narrow the matches is to add further characters in the pattern; for example, Ap* would match character sequences such as April but not August. Another option is to use multiple ? metacharacters, each of which would match a single character, so that you specify the number of characters that you want to match; for example, the pattern Ap??? would match April.

358