How to find and replace tildes and wildcard characters in Excel

Article translations Article translations
Article ID: 214138 - View products that this article applies to.
This article was previously published under Q214138
For a Microsoft Excel for Macintosh version of this article, see 321819.
Expand all | Collapse all

On This Page

SUMMARY

Microsoft Excel uses the tilde (~) as a marker to indicate that the next character is a literal. When you use the Find and Replace dialog box to find or replace a character such as a tilde (~), an asterisk (*), or a question mark (?), you must add a tilde (~) before the character in the Find what box.

Note If you want to find or replace a tilde in a worksheet, you must type a double tilde (~~) in the Find what box.

MORE INFORMATION

Example 1: How to find and to replace numbers

To replace wildcard characters (*) in a numeric value in a worksheet cell, follow these steps:
  1. Type 494** in cell A1.
  2. Select cell A1.
  3. On the Edit menu, click Replace.

    Note In Microsoft Office Excel 2007, click Find & Select in the Editing group on the Home tab, and then click Replace.
  4. In the Find what box, type ~*. To do this, 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 to replace a tilde

To replace a tilde in an Excel worksheet cell, follow these steps:
  1. Type Micros~1.xls in cell A1.
  2. Select cell A1.
  3. On the Edit menu, click Replace.

    Note In Excel 2007, click Find & Select in the Editing group on the Home tab, and then 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.

Properties

Article ID: 214138 - Last Review: January 23, 2007 - Revision: 4.2
APPLIES TO
  • Microsoft Office Excel 2007
  • Microsoft Office Excel 2003
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 2000 Standard Edition
  • Microsoft Excel 97 Standard Edition
Keywords: 
kbexpertisebeginner kbhowto kbui KB214138

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com