Automatic addition of year to NON-date values

mhardy

New Member
Joined
Mar 5, 2002
Messages
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
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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,
 
Upvote 0
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
 
Upvote 0
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?
:confused:
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top