+ Reply to Thread
Results 1 to 5 of 5

Excel keeps converting text to date format

  1. #1
    John T via OfficeKB.com
    Guest

    Excel keeps converting text to date format

    How can I keep Excel from changing the format of a cell from "Text" to "Date?
    " I use Excel 2002 SP3. I am entering a column of file names of the form
    "01-01-01." Excel immediately presents that as "1/1/2001" So I then change
    the format of the cell to "Text" and it changes the cell entry to "36892",
    the day equivalent. So, I format the cell as text before typing in the entry,
    and, as I would hope, it gives me "01-01-01" Now the frustration begins.
    The file names have a repeating pattern, so I want to be able to copy a block
    and do a "Find and Replace" on one part of it - for example to change "01-01-
    01" "01-01-02" etc to "01-02-01" "01-02-02" etc. I copy the block of values
    into a block of cells pre-fomatted as dates, and they present correctly.
    When I do the Replace, however, Excel automatically changes the format of the
    cells whose values I'm replacing to date, so instead of "01-02-01" I get
    "1/2/2001" Is there a way to turn off this excruiatingly annoying "smart"
    feature or do I have to type in all my text values preceded with a single
    quote? Seems like I was doing that with the first version of Supercalc, but
    I hoped we had progressed since then.
    John

  2. #2
    Bill Martin
    Guest

    Re: Excel keeps converting text to date format

    John T via OfficeKB.com wrote:
    > How can I keep Excel from changing the format of a cell from "Text" to "Date?
    > " I use Excel 2002 SP3. I am entering a column of file names of the form
    > "01-01-01." Excel immediately presents that as "1/1/2001" So I then change
    > the format of the cell to "Text" and it changes the cell entry to "36892",
    > the day equivalent. So, I format the cell as text before typing in the entry,
    > and, as I would hope, it gives me "01-01-01" Now the frustration begins.
    > The file names have a repeating pattern, so I want to be able to copy a block
    > and do a "Find and Replace" on one part of it - for example to change "01-01-
    > 01" "01-01-02" etc to "01-02-01" "01-02-02" etc. I copy the block of values
    > into a block of cells pre-fomatted as dates, and they present correctly.
    > When I do the Replace, however, Excel automatically changes the format of the
    > cells whose values I'm replacing to date, so instead of "01-02-01" I get
    > "1/2/2001" Is there a way to turn off this excruiatingly annoying "smart"
    > feature or do I have to type in all my text values preceded with a single
    > quote? Seems like I was doing that with the first version of Supercalc, but
    > I hoped we had progressed since then.
    > John



    Try formatting the column of cells as text *before* entering the data...

    Bill

  3. #3
    John T via OfficeKB.com
    Guest

    Re: Excel keeps converting text to date format

    Yes, I did that. The problem is that when I do a "Replace" (but not when I
    type in a new value), Excel undoes that and reformats it as date. I think it
    must be a bug in the "Replace" function, and I'm wondering if there's a
    workaround other than preceding every entry with a single quote.

    John

    Bill Martin wrote:
    >
    >Try formatting the column of cells as text *before* entering the data...
    >
    >Bill



    --
    Message posted via http://www.officekb.com

  4. #4
    Bill Martin
    Guest

    Re: Excel keeps converting text to date format

    John T via OfficeKB.com wrote:
    > Yes, I did that. The problem is that when I do a "Replace" (but not when I
    > type in a new value), Excel undoes that and reformats it as date. I think it
    > must be a bug in the "Replace" function, and I'm wondering if there's a
    > workaround other than preceding every entry with a single quote.
    >
    > John
    >
    > Bill Martin wrote:
    >
    >>Try formatting the column of cells as text *before* entering the data...
    >>
    >>Bill

    -------------------

    Not that I'm aware of. One solution would be to write a macro that corrects the
    error after the fact. After doing all your Replaces you'd hit a button to
    automatically find any entries in the wrong format and correct them back again.

    It's an inelegant solution, but it is a solution...

    Bill

  5. #5
    Bill Martin
    Guest

    Re: Excel keeps converting text to date format

    John T via OfficeKB.com wrote:
    > Yes, I did that. The problem is that when I do a "Replace" (but not when I
    > type in a new value), Excel undoes that and reformats it as date. I think it
    > must be a bug in the "Replace" function, and I'm wondering if there's a
    > workaround other than preceding every entry with a single quote.
    >
    > John
    >
    > Bill Martin wrote:
    >
    >>Try formatting the column of cells as text *before* entering the data...
    >>
    >>Bill

    -------------------

    Not that I'm aware of. One solution would be to write a macro that corrects the
    error after the fact. After doing all your Replaces you'd hit a button to
    automatically find any entries in the wrong format and correct them back again.

    It's an inelegant solution, but it is a solution...

    Better yet, write your own custom "Replace" macro that works properly. And add
    whatever tweaks make it even better for your own particular problem. I agree
    this should not be necessary in an ideal world, but IMHO it's the cleanest solution.

    Bill

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1