Paste changes ratios to dates

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

Guest

I'm cutting a table from Yahoo! Finance and pasting it into a worksheet. One
of the columns is called Ratio, and it's expressed as 3/2, 2/1 etc. Excel
thinks it's a date, formats it as March 2, 2005, Februrary 1, 2005 etc. and
stores it as a date number. I've tried preformatting the paste target as
text, but Excel is very stubborn, and persists in storing the data as date
number. If a do a paste special, I lose all the tabs, so can't do
text-to-table. How do I force it to paste everything as text?
 
Could you possibly manipulate your input to change those slashes
to colons for your ratios, such as in Notepad..

I think if you format the columns as text first that the paste might
work. Or maybe the paste will wipe out the Text formatting.-
 
Actually, in this case the ratios are just for information, so formatting
them as mm/dd happens to make them look correct, even though the underlying
data is now wrong. I did try a series of steps that worked relatively easily:
Paste into Word
Select the table,
Table/Convert/table to text, delimited by semicolon
Select
Edit/Copy
Paste special (text) into Excel
Data/Text to Columns
Change selected columns to text

It seems very silly to me that just because data happens to look like a date
(or a decimal number, for that matter) that there should be no way to tell
Excel "I know what you think this is, but you're wrong". I frequently paste
hex values into a spreadsheet, for example. and those that contain letters
paste as text, whereas those that don't paste as numbers.
I did try a series of steps that did work relatively easily:
Paste into Word
Select the table, convert table to text, delimited by semicolon
Copy text
Paste special (text) into Excel
Data/text to columns
Change selected columns to text
 
Back
Top