Excel 2003 thinks things are dates

Topic

New Reply

I am in the process of entering a set of data into Excel. Some of the data is in the form of 4-55. Put that into a “general” formatted cell and it changes it into a date–a totally different value.

OK–so I go to the cell formatting and change it to “Text”. I now get the text to stay in the form of 4-55…………BUT…………….

Go to sort, and Excel still thinks things are dates, and I get the following type of result:
4-55
1-66
3-291
Obviously Excel still thinks that 4-55 and 1-66 are dates, and is simply letting me see what I entered.

IS THERE ANY WAY TO STOP THE MADNESS???

Reply | Quote

Replies

Add a space to the front of the text on entry

Reply | Quote

Add a space to the front of the text on entry

Hmm–I have heard worse ideas. Thanks.

[But seriously someone should send a message to Microsoft–isn’t this a bit draconian even on your part. I mean, no way to shut it off?]

Reply | Quote

Hmm–I have heard worse ideas. Thanks.

[But seriously someone should send a message to Microsoft–isn’t this a bit draconian even on your part. I mean, no way to shut it off?]

Yep, that is a work around but if you want a way to format the cells try a custom format of @

Reply | Quote

Yep, that is a work around but if you want a way to format the cells try a custom format of @

Thanks even more. Yes, that works even better (I tried it in a sandbox). But, oh my, was that a nusiance to dig out of the “help” files.

Reply | Quote

I am in the process of entering a set of data into Excel. Some of the data is in the form of 4-55. Put that into a “general” formatted cell and it changes it into a date–a totally different value.

OK–so I go to the cell formatting and change it to “Text”. I now get the text to stay in the form of 4-55…………BUT…………….

Go to sort, and Excel still thinks things are dates, and I get the following type of result:
4-55
1-66
3-291
Obviously Excel still thinks that 4-55 and 1-66 are dates, and is simply letting me see what I entered.

IS THERE ANY WAY TO STOP THE MADNESS???

An alternative is to Type a single ‘ at the start rather than a space.

‘4-55 is treated as text. The ‘ does not show in the cell, and is not used if you take the content of the cell

So for example Len with Space4-55 would return 5 but the correct 4 with ‘4-55.

The ‘ is ignored by Excel, it just tells is it to treat the entry as Text rather than interpret in normal way.
I would use this method rather than the space.

Reply | Quote

It’s more cumbersome, but if you use the Data-Sort dialog (rather than the A-Z buttons) then tell it to sort numbers stored as text separately, it will sort your original data as it should. Once you have done that once, you should be able to use the A-Z and Z-A buttons as usual.

Reply | Quote