Unwanted automatic date format

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
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
 
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.
 
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
 
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?
 
Back
Top