Microsoft KB Archive/142895

= Microsoft Knowledge Base =

No Match Errors with Replace and Lead Apostrophe
Last reviewed: September 13, 1996

Article ID: Q142895

The information in this article applies to:


 * Microsoft Excel for Windows, versions 4.x, 5.0, 5.0c
 * Microsoft Excel for Windows NT, version 5.0
 * Microsoft Excel for Windows 95, version 7.0
 * Microsoft Excel for the Macintosh, versions 5.0, 5.0a
 * Microsoft Excel for the Power Macintosh, versions 5.0, 5.0a

If you are using Microsoft Excel version 5.0 or later
If you click Replace on the Edit menu, select the "Find Entire Cells Only" option, and then click the Replace All button, Microsoft Excel may return the following error message

No Match in Active Cell and your only option is to click OK.

If you are using Microsoft Excel version 4.0
If you click Replace on the Formula menu, select Whole for the Look At option, and then click the Replace All button, Microsoft Excel may return the following error message

No Match in Current Cell and your only option is to click OK.

CAUSE
You receive the error message corresponding to the version of Excel you are using in Microsoft Excel if the following conditions exist:

 Microsoft Excel finds a cell that matches your search entry in the Find What box. -and-  The matching cell contains a leading apostrophe. NOTE: This problem may also occur if the matching cell contains a leading caret (^) or leading quotation mark ("). 

WORKAROUND
To work around this problem, when you use the Replace option in Microsoft Excel, precede your string in the Find What box with the asterisk (*) wildcard character. The Replace option is located on either the Edit menu or the Formula menu depending on the version of Microsoft Excel you are using.

Note that the asterisk (*) wildcard character should be used only in cases where the cells have a leading apostrophe (or other leading characters) before the text you want to replace. When you use the asterisk (*) wildcard character, Microsoft Excel replaces any preceding characters before the matching string.

For instance, if you type "*test" (without the quotation marks) in the Find What box and type "passed" (without the quotation marks) in the Replace With box, Microsoft Excel would replace not only "'test" with "passed", but also any cell in your search area that has text before the word "test." For example, "this is a test" would also be replaced with "passed."

STATUS
Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. We are researching this problem and will post new information as it becomes available.

MORE INFORMATION
If you perform a search on a range of cells, or if you search your entire worksheet, you may receive the error message described above for each cell in the range (or worksheet) for which it should find a match. Therefore, you may have to click the OK button in the error message window for each occurrence that Microsoft Excel reads as a match. If you have a large number of matches, you may have to hold down the ENTER key until Microsoft Excel completes the search.

In any version of Microsoft Excel running under Windows 95, in Microsoft Excel for Windows NT, or in Microsoft Excel for the Macintosh, you can force Microsoft Excel to shut down; however, if you choose to shut down Microsoft Excel, you will lose all changes to all files that are currently open in Microsoft Excel.

For more information about how to perform this task in Windows, see your Windows printed documentation or online Help.

To force the program to quit in Microsoft Excel for the Macintosh, press COMMAND+OPTION+ESC, and then click the Force Quit button in the dialog box that appears.

In previous versions of Microsoft Windows, you are unable to shut down Microsoft Excel.

Caret and Quotation Mark Leading Characters
If the matching cell contains a leading caret or leading quotation mark, you may receive the error message described earlier in this article. You are likely to see these leading characters in cells if you open a Lotus 1-2- 3 file or if you manually type these leading characters into a cell. Lotus 1-2-3 uses the leading characters for text alignment and text format definition. The caret and quotation mark as leading characters are visible in Microsoft Excel if you select the Transition Navigation Keys check box. To find this check box, click Options on the Tools menu, and then click the Transition tab.

Microsoft Excel Version 7.0
For more information about the Replace Command in Microsoft Excel version 7.0, click the Index Tab in Microsoft Excel Help, type the following text

Replace Command and then double-click the selected text to go to the "Replace command (Edit menu)" topic.

Microsoft Excel Versions 5.0 and Earlier
"User's Guide 1," version 4.0 for Windows, Chapter 6, "Editing a Worksheet"

"User's Guide," version 5.0 for Windows, Chapter 11, "Editing a Worksheet"

For more information about the Replace Command in Microsoft Excel versions 5.0 and earlier, click the Search button in Help and type:

Replace