Automatic addition of year to NON-date values

mhardy · Mar 6th, 2002, 03:05 PM

I have an xls with a number of values in the format '...X-Y...'. e.g. '...3-6...'. After I execute a search/replace to clean up my data and remove all the dots (.) excel always adds '2002' to these values and converts them to dates. I then lose my original values.

I've specified that all values should be text, but the search/replace seems to ignore this and still make them dates.

Is there any way to turn off excel's automatic date creation and leave my data as is. *So* annoying. I've tried to disable all AutoCorrect crap but still no luck.

Thanks-
Mike

Barrie Davidson · Mar 6th, 2002, 03:17 PM

Quote:

On 2002-03-06 14:05, mhardy wrote:
I have an xls with a number of values in the format '...X-Y...'. e.g. '...3-6...'. After I execute a search/replace to clean up my data and remove all the dots (.) excel always adds '2002' to these values and converts them to dates. I then lose my original values.

I've specified that all values should be text, but the search/replace seems to ignore this and still make them dates.

Is there any way to turn off excel's automatic date creation and leave my data as is. *So* annoying. I've tried to disable all AutoCorrect crap but still no luck.

Thanks-
Mike

Make sure your cells are formatted as text before replacing. I'll bet they are currently formatted as General.

Regards,

mhardy · Mar 6th, 2002, 03:53 PM

That's the real kicker. The entire sheet is formatted Text. I've even tried a new sheet with only one cell containing: ...3-6..., formatted as Text, then search/replace '.', with nothing. Replace All, then bang - 3/6/2002. *argh*.

-Mike

Barrie Davidson · Mar 6th, 2002, 03:59 PM

Quote:

On 2002-03-06 14:53, mhardy wrote:
That's the real kicker. The entire sheet is formatted Text. I've even tried a new sheet with only one cell containing: ...3-6..., formatted as Text, then search/replace '.', with nothing. Replace All, then bang - 3/6/2002. *argh*.

-Mike

Hmmm....how about a formula approach? In an adjacent column, use the SUBSTITUTE function and then paste that value over your old data. For example,
=SUBSTITUTE(A1,"...","")

This, for sure, will return a string value.

What do you think?

Mark W. · Mar 6th, 2002, 04:10 PM

Use the Data | Text to Columns... menu command.

1. Select a column of your data.
2. Choose the Data | Text to Columns... menu command and specify "Delimited" at Step 1 of 3
3. At Step 2 of 3 check "Other" delimiter, enter "." in the "Other" input field, and check "Treat consecutive delimiters as one"
4. You have now specified 2 columns. At Step 3 of 3 the 1st column should be assigned "Do not import column (Skip)" as its data format and the 2nd should be set to "Text".

That should do it!!

[ This Message was edited by: Mark W. on 2002-03-06 15:31 ]

Barrie Davidson · Mar 6th, 2002, 04:12 PM

Mark, that's way better! Too bad I didn't read the question as posed (haste makes waste).

Regards,

mhardy · Mar 6th, 2002, 04:52 PM

Thanks - that did do the trick. Unfortunately I have 12 pages, 10 columns per page and Text to Column only works 1 column at a time. However, that did spark an idea. I've exported as a .csv, search/replaced all my dots out, then re-imported as Text. A little quicker but I still have to do it 12 times.

Sure seems like a lot of work when search/replace gets me 99% of the way there. I'm surprised there's not a way to turn this 'feature' off.

Thanks for the quick help!
Mike

Mar 6th, 2002, 04:12 PM	#6
Barrie Davidson MrExcel MVP Join Date: Feb 2002 Location: Winnipeg Posts: 2,330	Mark, that's way better! Too bad I didn't read the question as posed (haste makes waste). Regards, __________________ Barrie Davidson "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 6th, 2002, 03:05 PM	#1
mhardy New Member Join Date: Mar 2002 Posts: 3	I have an xls with a number of values in the format '...X-Y...'. e.g. '...3-6...'. After I execute a search/replace to clean up my data and remove all the dots (.) excel always adds '2002' to these values and converts them to dates. I then lose my original values. I've specified that all values should be text, but the search/replace seems to ignore this and still make them dates. Is there any way to turn off excel's automatic date creation and leave my data as is. So annoying. I've tried to disable all AutoCorrect crap but still no luck. Thanks- Mike

Mar 6th, 2002, 03:53 PM	#3
mhardy New Member Join Date: Mar 2002 Posts: 3	That's the real kicker. The entire sheet is formatted Text. I've even tried a new sheet with only one cell containing: ...3-6..., formatted as Text, then search/replace '.', with nothing. Replace All, then bang - 3/6/2002. argh. -Mike

Mar 6th, 2002, 04:10 PM	#5
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	Use the Data \| Text to Columns... menu command. 1. Select a column of your data. 2. Choose the Data \| Text to Columns... menu command and specify "Delimited" at Step 1 of 3 3. At Step 2 of 3 check "Other" delimiter, enter "." in the "Other" input field, and check "Treat consecutive delimiters as one" 4. You have now specified 2 columns. At Step 3 of 3 the 1st column should be assigned "Do not import column (Skip)" as its data format and the 2nd should be set to "Text". That should do it!! [ This Message was edited by: Mark W. on 2002-03-06 15:31 ]

Mar 6th, 2002, 04:52 PM	#7
mhardy New Member Join Date: Mar 2002 Posts: 3	Thanks - that did do the trick. Unfortunately I have 12 pages, 10 columns per page and Text to Column only works 1 column at a time. However, that did spark an idea. I've exported as a .csv, search/replaced all my dots out, then re-imported as Text. A little quicker but I still have to do it 12 times. Sure seems like a lot of work when search/replace gets me 99% of the way there. I'm surprised there's not a way to turn this 'feature' off. Thanks for the quick help! Mike