How to find and replace tildes and wildcard characters in Excel (214138)


The information in this article applies to:

  • Microsoft Office Excel 2003
  • Microsoft Excel 2002
  • Microsoft Excel 2000
  • Microsoft Excel 97 for Windows
This article was previously published under Q214138
For a Microsoft Excel for Macintosh version of this article, see 321819.

SUMMARY

Microsoft Excel uses the tilde (~) as a marker to indicate that the next character is a literal. When you click Find or Replace on the Edit menu to replace or search for a tilde (~), an asterisk (*), or a question mark (?), you must precede the character with a tilde (~).

NOTE: If you want to find or replace a tilde in your worksheet, you must type a double tilde (~~).

MORE INFORMATION

Example 1: How to Find and Replace Numbers

To replace wildcard characters (*) in a numeric value in a worksheet cell, use the steps in the following example:
  1. Type 494** in cell A1.
  2. Select cell A1.
  3. On the Edit menu, click Replace.
  4. In the Find what box, type ~* (press TILDE, and then press ASTERISK).
  5. In the Replace with box, type 2.
  6. Click Replace.
Cell A1 now reads 49422.

NOTES:
  • When you click Replace All, Excel makes the change throughout the worksheet. When you click Replace, Excel changes only the currently active cell and leave the Replace dialog box open.
  • When you type an asterisk without a tilde in the Find what box, Excel replaces all entries with a 2. Excel treats the asterisk as a wildcard character. Therefore, 494** becomes 2.

Example 2: How to Find and Replace a Tilde

To replace a tilde in an Excel worksheet cell, use the steps in the following example:
  1. Type Micros~1.xls in cell A1.
  2. Select cell A1.
  3. On the Edit menu, click Replace.
  4. In the Find what box, type:

    ~~

  5. In the Replace with box, type:

    oft

  6. Click Replace All.
Cell A1 now reads Microsoft1.xls.

REFERENCES

For more information about how to find and replace wildcard characters, click Microsoft Excel Help on the Help menu, type wildcard characters you can use to find text or numbers in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
Modification Type:MinorLast Reviewed:11/14/2005
Keywords:kbhowto kbui KB214138
©2004 Microsoft Corporation. All rights reserved.