Formatting when copying SQL data and pasting in Excel

Question

I want to copy a sql result set and paste it in Excel. But the data I paste in to the spreadsheet doesn't want to recognize Excel formatting. So if I change a column to currency, it doesn't do anything. But...if I double click on a cell, THEN it applies the currency format. But only to that cell.

How can I make it automatically recognize the Excel format?

I must be something I'm missing. Hopefully somebody can help. :-)

Answer 1

Have you tried <edit><paste special> and then paste the data as text? Otherwise you're copying data AND formatting, which might be the problem.

Answer 2

Seconding the <edit><paste special>, but another useful feature is <Text to Columns>. So if you paste your data as text, if it does not automatically appear in columns, use the Text to Columns to turn it into columns, then any formatting you apply should work.