Automatic addition of year to NON-date values

[mhardy]

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]

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]

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]

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.]

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]

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

Regards,

 

[mhardy]

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