XL: Can't Use Find/Replace on Text with Leading Apostrophe

ID: Q124739

The information in this article applies to:

SYMPTOMS

In Microsoft Excel, if you type text that begins with an apostrophe character (') in the Find What box in the Find or Replace dialog box, and then click Find Next, you receive one of the following error messages: 

   Cannot find matching data

    -or-

   Microsoft Excel cannot find the data you're searching for. If you are
   certain the data exists in the current sheet, check what you typed and
   try again.
If you click Replace All, you receive one of the following error messages: 

   Cannot find matching data to replace

    -or-

   Microsoft Excel cannot find matching data to replace. No cell in the
   selection contains what you typed, or no records match the criteria.

CAUSE

The engine for the Find and Find Next commands does not recognize a leading apostrophe character because this character in this position is designed to be transparent.

WORKAROUND

To work around this problem, use any of the following methods.

Method 1

Replace the contents of a single cell that contains text that begins with an apostrophe character. To do this, follow these steps:

1. Select the cell that contains the text that you want to replace, and 

   click Replace on the Edit menu.
2. In the Find What box, type the text that you want to replace, such as 
   "xxx" (without the quotation marks). Do not include the apostrophe.
3. In the Replace With box, type the text that you want to appear in the 
   cell, such as "zzz" (without the quotation marks) and click Replace.

Method 2

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web: 

   http://www.microsoft.com/support/supportnet/refguide/
Replace the contents of multiple cells that contain text entries that begin with apostrophes. To do this, use the following Visual Basic for Applications procedure to remove hidden leading apostrophes from a selected range of cells. 

   ' Removes hidden apostrophes as first character
   ' Works on formulas as well as text and values

   Sub RemoveApostrophe()

       For Each CurrentCell In Selection
           If CurrentCell.HasFormula = False Then

               'Checks to make sure that procedure does not
               'change cell with a formula to be only the value
               CurrentCell.Formula = CurrentCell.Value
           End If
       Next

   End Sub

Method 3

Replace the contents of a single cell, or multiple cells that contain numbers that begin with an apostrophe character. To do this, follow these steps:

1. In an empty cell on the worksheet, type the value 1.

2. Select the cell that contains the value 1, and click Copy on the Edit 

   menu.
3. Select the cells that contain the text that you want to replace, and 
   then click Paste Special on the Edit menu.
4. Under Paste, click Values. Under Operation, click Multiply, and then 
   click OK.

REFERENCES

For more information about finding and replacing text or numbers, choose the Search button in Help and type: 

    replacing
Additional query words: XL97 97 7.00 5.00 5.00c 
Keywords          : kbprg kbualink97 kbdta kbdtacode xlvbahowto xlformula 
Version           : WINDOWS:5.0,5.0c,7.0,97; MACINTOSH:5.0
Platform          : MACINTOSH WINDOWS
Issue type        : kbprb

Last Reviewed: May 24, 1999