You are currently offline, waiting for your internet to reconnect

How to find and replace tildes and wildcard characters in Excel

Support for Office 2003 has ended

Microsoft ended support for Office 2003 on April 8, 2014. This change has affected your software updates and security options. Learn what this means for you and how to stay protected.

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 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.
XL2007 XL2003 XL2002 XL2000 XL97
Properties

Article ID: 214138 - Last Review: 01/23/2007 18:24:50 - Revision: 4.2

Microsoft Office Excel 2007, Microsoft Office Excel 2003, Microsoft Excel 2002 Standard Edition, Microsoft Excel 2000 Standard Edition, Microsoft Excel 97 Standard Edition

  • kbexpertisebeginner kbhowto kbui KB214138
Feedback
if?DI=4050&did=1&t=">