Format of data pasted into worksheet

[vjh]

Hi all,

How do I prevent data that I paste in from getting magically transformed into dates. I tried pasting it in as plain text, but lost the format of the columns. I tried changing the format of the cells before I pasted it in, but that didn't work either.

How annoying! A permanent solution to this perennial problem would be wonderful!!

...vj

 

[MarkBeck]

This depends on the Data type.

First try using "Paste special" from the edit menu or right-click, paste the values only and ensure that your destination has a numerical format.

If the data you are trying to paste are dates and your problem is that the dates are transposed (from "mm dd yy" to "dd mm yy", so 8 June becomes 6 August), you can try the same thing but make sure that your destination is set as a date format.

You can also try to simply reset the date style of your computer to match the date type of your data. you can do this from the Regional settings in Control Panel click the Date tab and reset the style


If i have missed your point, please restate your question in more detail.

Good luck

Mark

 

[vjh]

Thanks for your reply.

I tried the Paste Special Approach, and it still transformed data.

What I have are numbers that represent ranges. They are formatted in my database as 2-45 and 1-3. If Excel can translate it into a date, it does. So the first one pastes fine, and the second one becomes a date (March 1). If I then change it back to text, I get 37681.

Very frustrating.

I ended up pasting it into word first, replacing all the - with *-, changing the format of the cells to text, then replacing *- with -. Works, but how annoying!

Since I'm often dealing with these things, I thought I'd ask the pros.

I'll try your suggestion using the Regional settings. See if that works.

Thanks again.

... vj

 

[MarkBeck]

In that case the answer is to import them as text. make sure that both your original and destination columns are formatted as text (from the FORMAT menu click Cells and from the NUMBER tab choose Text). When pasted via "Paste special" this will signal to Excell that you want it the way it appears.

The downside is that this then converts the numeric value of a date/ time to text and subsequently one cannot benefit from them mathematicaly.

 

[PeterMoran]

Hi vjh,

Several thoughts here:

If you are keying these fields initially then key a space first, this certainly stops the problem.

Or use a formula like =" "&A1 in B1 where col A is the column causing the problem, and then copy down the column, so everything has a space first.

Also you could change all the dashes to decimal points - this will also stop the problem.

Good Luck!

Peter Moran
Two heads are always better than one!

 

[vjh]

Thanks for all the suggestions... Sadly, none of them have worked in my case.

I can't change the structure of the data I'm importing It's from an Oracle database that requires the dashes exactly as they are structured. Formatting the cells as text before pasting them in has no effect.

Guess this will just be one of those things that I have to work around!

Thanks again, V

 

[zdas04]

I've got the same problem (in Excel XP). Pasting an HTML table from a website (that I have no control over) with values like 1-5 gets translated into wierd dates. The format of the worksheet is completely overwritten, paste special gives me options like "HTML", "Unicode Text" and "Text&quot. HTML yeilds the results above. "Unicode text" and "text" makes the file kind of space delimited (problem is that many of the HTML cells have imbedded spaces).

The easiest technique is to use "Paste Special" "Text", "Data", Text to Columns" and then re-combine cells WHAT A PAIN, but it is easier than formating the columns with ranges to "text" and then replacing 98774 with 1-3 a hundred times a week.

I really just want to tell Excel that "-" does NOT mean "date" ever.

David

 

[MarkBeck]

Hi! David

I sometimes import stuff even with spaces but subsequently eliminate the spaces using the "Replace" command (Ctrl + H). This leaves you clean data which you can then reformat.