XL: Replacing Text May Change Text to Numbers or Dates

ID: Q178606

The information in this article applies to:

SYMPTOMS

In the versions of Microsoft Excel listed at the beginning of this article, 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 resembles a number, date, or time. If the contents of a cell resembles 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 will not automatically reformat cells if the Transition Formula Entry (TFE) option is turned on. To turn on this option, follow these steps:

1. On the Tools menu, click Options. Select the Transition tab.

2. Click to select the Transition Formula Entry check box. 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. Select the Transition tab.

5. Click to clear the Transition Formula Entry check box. Click OK.

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article.

MORE INFORMATION

The following example demonstrates the problem described in the "Symptoms" section.

1. In Microsoft Excel, create a new workbook.

2. In Sheet1, select cells A1:A5. On the Format menu, click Cells. Select 

   the Number tab. In the Category list, select Text. Then, click OK.

   Cells A1:A5 are now formatted to display text.
3. Enter the following values: 

      A1: 3/8"
      A2: 2:30"
      A3: 6e5"

      A5: 95
4. On the Edit menu, click Replace.

5. In the Find What edit box, enter a single quotation mark ("). Do not 

   enter any text in the Replace With edit box. Then, click Replace All.

   The contents and formatting of the cells are changed as follows

      A1: 8-Mar (or 3-Aug, depending on your regional settings)
      A2: 2:30
      A3: 6.00E+05

   even though the cells were originally formatted to display text. Cell
   A1 was changed from 3/8" to 3/8. Because 3/8 resembles a date, Microsoft
   Excel automatically applies a date format to the cell. The same rule
   applies to cell A2, which resembles a time, and cell A3, which resembles
   a number entered using scientific notation.
6. Select cell A5.

7. On the Edit menu, click Replace.

8. In the Find What edit box, enter the number 9. In the Replace With 

   edit box, type "9e" (without the quotation marks). Then, click Replace.

   Cell A5 displays 9.00E+05, rather than 9e5, even though cell A5 was
   originally formatted to display text.
9. Click Close.

Additional query words: XL5 XL7 XL97 

Keywords          : xlformat xlformula 
Version           : MACINTOSH:5.0,5.0a,98; WINDOWS:5.0,5.0c,7.0,7.0a,97
Platform          : MACINTOSH WINDOWS
Issue type        : kbprb

Last Reviewed: January 8, 1999