XL2000: Replacing Text May Change Text to Numbers or Dates (214339)


The information in this article applies to:

  • Microsoft Excel 2000
This article was previously published under Q214339

SYMPTOMS

If you replace characters in a worksheet by clicking Replace on the Edit menu, the formatting of some cells in the worksheet may unexpectedly be changed, for example: 
   What Should Appear      What Actually Appears
   -----------------------------------------------------

   2:30:00 (as text)       2:30:00 (as a time)
   3/8 (as text)           8-Mar or 3-Aug (as a date)
   9e5 (as text)           9.00E+05 (scientific notation)

CAUSE

This problem may occur when text is being replaced in a cell and the contents of the cell resemble a number, date, or time. If the contents of a cell resemble a number, date, or time, Microsoft Excel automatically applies an appropriate number format to the cell, even if the cell was previously formatted to display text.

WORKAROUND

Microsoft Excel does not automatically reformat cells if the Transition formula entry check box is selected. To select this check box, follow these steps:

NOTE: This workaround may not fix the problem with the "9e5" entry. The "9e5" entry may still change to "9.00E+05" even with the Transition formula entry check box selected.
  1. On the Tools menu, click Options.
  2. On the Transition tab, under Sheet Options, click to select the Transition formula entry check box, and then click OK.
  3. On the Edit menu, click Replace. Use the Replace dialog box to replace text as needed in the worksheet. Click Close when you are finished.
  4. On the Tools menu, click Options.
  5. On the Transition tab, click to clear the Transition formula entry check box, and then click OK.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.
Modification Type:MajorLast Reviewed:9/27/2003
Keywords:kbbug kbpending KB214339
©2003 Microsoft Corporation. All rights reserved.