Select the product you need help with

How to find and replace tildes and wildcard characters in Excel

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

(http://support.microsoft.com/kb/321819/ )

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.

Back to the top | Give Feedback

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:

Type 494** in cell A1.
Select cell A1.
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.
In the Find what box, type ~*. To do this, press TILDE, and then press ASTERISK.
In the Replace with box, type 2.
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:

Type Micros~1.xls in cell A1.
Select cell A1.
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.
In the Find what box, type ~~.
In the Replace with box, type oft.
Click Replace All.