Microsoft KB Archive/214285

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

Article ID: 214285

Article Last Modified on 6/23/2005

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition

-



This article was previously published under Q214285





SYMPTOMS
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 may receive the following error message:

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 may receive the following error message:

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, in this position, this character is designed to be transparent.



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

Method 1: To Replace the Contents of a Single Cell
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 . 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 and click Replace.

Method 2: To Replace the Contents of Multiple Cells
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers 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 requirements. 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: Use Copy and Paste
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.

Additional query words: xl2000

Keywords: kbualink97 kbprogramming kbprb kbdtacode KB214285

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.