XL: Finding/Replacing Tildes and Wildcard Characters

• Microsoft Excel for Windows, versions 3.0, 4.0, 5.0, 5.0c
• Microsoft Excel for Windows 95, versions 7.0, 7.0a
• Microsoft Excel 97 for Windows
• Microsoft Excel for the Macintosh, versions 2.2, 3.0, 4.0, 5.0
• Microsoft Excel 98 Macintosh Edition

SUMMARY

In Microsoft Excel, when you use Find or Replace to replace or search for a tilde (~), an asterisk (*), or a question mark (?), the character must be preceded with a tilde (~).

MORE INFORMATION

The tilde is used as a marker to denote that the next character is a literal. Microsoft Excel reads the character following a single tilde as the character to be found or replaced. If you want to find or replace a tilde in your worksheet, you must enter a double tilde (~~). Similarly, when you want to find or replace a wildcard character (* or ?), the wildcard character must be preceded with a tilde.

Example 1

Suppose that you enter 494** in cell A1 and that you want to replace each asterisk with the number 2. To do this, use the following appropriate method.

In Excel 5.0 or Later

To replace the asterisk, follow these steps:

1. Select cell A1.

2. On the Edit menu, click Replace.

3. In the Find What box, type ~* (press TILDE, ASTERISK).

4. In the Replace With box, type 2.

5. Click Replace.

In Excel 4.0 or earlier

To replace the asterisk, follow these steps:

1. Select cell A1.

2. On the Formula menu, click Replace.

3. In the Replace box, type ~* (press TILDE, ASTERISK).

4. In the With box, type 2.

5. Click Replace.

If there are other cells in the worksheet that contain asterisks, note that clicking Replace All will make the change throughout the worksheet. Clicking the Replace button will change only the currently active cell and will leave the Replace dialog box open.

Entering an asterisk without a tilde would replace all entries with a 2 because Microsoft Excel treats the asterisk as a wildcard. Therefore, 494** would become 2.

Example 2

To replace the text of "Micros~1.xls" in any cell with "Microsoft.xls", use the following appropriate method.

In Excel 5.0 or Later

To replace the text, follow these steps:

1. Select cell A1.

2. On the Edit menu, click Replace.

3. In the Find What box, type ~~ (TILDE, TILDE).

4. In the Replace With box, type "oft" (without the quotation marks).

5. Click Replace All.

In Excel 4.0 or Earlier

To replace the text, follow these steps:

1. Select cell A1.

2. On the Formula menu, click Replace.

3. In the Replace box, type ~~1 (press TILDE, TILDE).

4. In the With box, type "oft" (without the quotation marks).

5. Click the Replace All.

Microsoft Windows 95 enables you to use long file names. Therefore, when referencing an alias to a long file name, it may be necessary to change the tilde character within that alias across multiple cells in a worksheet. An alias is an abbreviated long file name that conforms to the MS-DOS 8.3 file naming convention.

NOTE: These methods do not apply to Microsoft Excel versions earlier than 2.x. These versions do not provide a method for locating wildcard characters as literal characters. Entering only a wildcard character in the Find or Replace command will find or replace every character in the document.

REFERENCES

"Microsoft Excel User's Guide 1," version 4.0, pages 202, 319

"Microsoft Excel User's Guide," version 3.0 for Windows, pages 169-170

"Microsoft Excel User's Guide," version 3.0 for the Macintosh, pages 163-164

"Microsoft Excel Reference," for the Macintosh, version 2.2, pages 353-354