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

 

[shg]

After selecting space as the delimiter, press Next, select all the fields, and tick Text.

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

Who puts hot dogs in potato salad?

 

[C with no eyes]

Who puts hot dogs in potato salad?

Completely agree. One can only hope he means fresh 90% pork frankfurters rather than tinned hot dogs.

How about instead of using 1 1/2 you change it to 1.5?

 

[steve case]

After selecting space as the delimiter, press Next, select all the fields, and tick Text.

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

<tbody>
</tbody>

Who puts hot dogs in potato salad?

Thanks that did work

Out of 8 recipes to compare there are some stranger things than hot dogs.

 

[steve case]

Completely agree. One can only hope he means fresh 90% pork frankfurters rather than tinned hot dogs.

How about instead of using 1 1/2 you change it to 1.5?

Thanks - this issue isn't for me to solve by reading down through several thousand lines
of stuff I down loaded off the net to see if there's something I should change in order to
avoid running afoul of the Excel Date Function.

I'm retired now - When I was employed I got calls to help with Excel and more than once
I ran into people pulling their hair out over the date function merry-go-round.

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

Microsoft needs to fix this sort of thing. A Format selection; Disable Date function as:
Default, this workbook, this sheet, this range - would be great.

Read German Potato Salad is great stuff.

 

[C with no eyes]

I understand but in situations like this I would be tempted just to find and replace all. Could be time consuming if there are loads of different fractions to change but with cooking it would only need to be halves, quarters and maybe eighths so it wouldn't take longer than 2 minutes.

CTRL+F then click replace tab. Replace [ 1/2] with [.5]. Replace all.

 

[steve case]

I understand but in situations like this I would be tempted just to find and replace all. Could be time consuming if there are loads of different fractions to change but with cooking it would only need to be halves, quarters and maybe eighths so it wouldn't take longer than 2 minutes.

CTRL+F then click replace tab. Replace [ 1/2] with [.5]. Replace all.

I've come up with work-arounds for all sorts of stuff. God helps those who help
themselves - but before I go to all that trouble I come to Mr. Excel - best bargain
on the internet, you get answers within minutes even on week-ends.

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?

 

[shg]

You could instead change your date format in Control Panel temporarily to something sans slash.

 

[steve case]

You could instead change your date format in Control Panel temporarily to something sans slash.

Hmmmm - do you mean under Format Cells or somewhere else?

 

[shg]

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

But I lied, it doesn't work.