Unwanted automatic date format

[Guest]

I am doing some cut and past, and also some search and replace for numbers
with a date-like format ("2-5", "2/5"), and Excell keeps interpreting these
as dates, and inserts the date format. When I reformat the cell to text, the
data is like "33679", I have lost the original data.

I have repeatedly set the cell format to text before doing the search and
replace, but for some reason, Excel still re-sets the format to Date.

I have also tried reseting both the source and target spreadsheets to the
text format before I cut and past. In both cases the problem persists.

Is there any way to disable the date format feature?

Or alternatively, force the cell format to text in all cases?

 

[Guest]

John

You will have to insert an apostrophe before the offending text before
importing it into Excel e.g '2-5 or '2/5

Peter

 

[Guest]

Thank you for your response.

Unfortunately I have over 12,000 entries to clean up. It would be easier to
simply edit each one rather than to try to insert an apostrophe in each.

All I am trying to do is to remove unit abbreviations (e.g. ft, in, mph,
etc.) from the numerical data.

What I don't understand is that some cells translate OK, while others change
to date format. I have tried removing all formatting and reapply the text
format, and it does the same thing.

There has to be a way to do this - I can not believe Microsoft would design
in a date format you could not get rid of.

 

[protonLeah]

Hello,
I tried to duplicate your problem within Excel but could not. I wa
only able to duplicate by cut/copy from MS Word. In that case I had t
choose
"paste special>text" . One choice is to write a small "paste special
macro and assign it to a key or button, thus:

Sub PASTESPECIAL()
Selection.NumberFormat = "@"
ActiveSheet.PASTESPECIAL Format:="Text", _
Link:=False, _
DisplayAsIcon:= False
end sub
:rolleyes

 

[Guest]

I tried the macro, and it seems to work well when brininging in stuff from
Word on a cell by cell basis. However, all of the data is in Excel - from
several combined worksheets.

The odd thing is that for one row, I manually changed every value - changed
format to text->entered the desired numbers (32, 36, etc.) and tried to
resort. The dialog box comes up ans says that I have a combination of text
and numbers!!??? How can this be? The cells only have a one or two digit
number - no text at all - I just re-entered every one of them.

Is there a way to cut and paste the entire spread sheet as text only?