Is it possible to defeat the date function:

[steve case]

Is it possible to defeat the date function?

Here's my text

5 Or 6 medium cooked potatoes,
1 Package hot dogs (optional)
1 1/2 ts Celery seed

Here's what I get on a Text to Columns Delimited (Space) selection:

5    Or    6    medium    cooked    potatoes,
1    Package    hot    dogs    (optional)    
1    2-Jan    ts    Celery    seed

Formatting the entire worksheet as "Text" does not help.

Editorial:

Over the years Exel's insistence on regarding some series of numbers as a date has in some instances caused serious errors. I know I'm not the only one who runs into this. Microsoft really needs to offer an option to disable the date function.

Background:

I'm comparing various recipes for German Potato Salad

I'm running and old version of Excel from Office 2000

 

[steve case]

No, I meant Control Panel > Region and Language Settings (in W7; dunno in W8/W10).

But I lied, it doesn't work.

But I lied, it doesn't work.

Ha ha ha ha ha ha ha ha ha ha ha ha ha ha!

Well, that's a first (-:

 

[shg]

I'm a little surprised that behavior is buried that deeply in Excel.

 

[steve case]

I'm a little surprised that behavior is buried that deeply in Excel.

I've had the same thought, the date function is buried in Excel's version of the reptilian brain
stem and an attempt at the correction I'd like to see would require a start from scratch re-write.

 

[Peter_SSs]

Another one is getting Excel to regard a blank as a blank instead of zero on a graph.

For me, a blank is treated as a blank in my graph, though still a little differently to what I am suggesting below.
Perhaps you mean a cell that isn't blank but contains a formula that is returning "" making the cell 'appear' blank? If that is the case, change your formula to return #N/A instead.
eg =IF(F3>5,G2,NA())
You can then use conditional formatting if you want to hide that #N/A value making the cell again 'appear' blank.

Besides the two issues I mentioned - when you download several thousand lines of
something, sort and delete a few thousand lines of crap - Excel still "Thinks" the
original several thousand lines are still there. Can't Microsoft fix this stuff?

If you Save the file again after deleting the data, does resolve the issue?

 

[steve case]

For me, a blank is treated as a blank in my graph, though still a little differently to what I am suggesting below.
Perhaps you mean a cell that isn't blank but contains a formula that is returning "" making the cell 'appear' blank? If that is the case, change your formula to return #N/A instead.
eg =IF(F3>5,G2,NA())
You can then use conditional formatting if you want to hide that #N/A value making the cell again 'appear' blank.


If you Save the file again after deleting the data, does resolve the issue?

Thanks, I'll try to remember the #N/A trick. Then Find and replace #N/A with nothing should complete the task (-:

With regard to Excel not recognizing where the end of the file is, I paste the new/shorter version onto Sheet2 and delete Sheet1. A simple work around - but I shouldn't have to do that. I shouldn't have to do the #N/A trick either.

Thank you everyone for all the responses - I really appreciate that ordinary users like little ol' me get taken care of.