Numbers as text when copied from ACCESS

[Jamie]

When I copy data from a table in MS Access and paste it into an Excel
spreadsheet each cell with a number has the error message that the number is
stored as text. This means that I have to convert all the cells individually
or do a rather clunky set of Paste Special moves to make them usable in
functions. Is there a way to prevent this from happening? This seems to be a
new "feature" of Excel 2003, I never had this problem using 2000.

Many thanks!

 

[Nick Hodge]

Jamie

I don't think it's new, it's just you may not have noticed it before.

There are a few ways around it.

1) The permanent solution would be to bring the data from a query in Access
into Excel via Data>External data, making sure, if it still did it, that the
query uses the VAL function to ensure the data are numbers

2) The other solution would be to copy a '1' in a spare cell and the
highlight the data and paste special>Values+Multiply. (If there is formulae
on the sheet, you may need to select the data using Edit>Go
to>Special...>Constants

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Nick Hodge]

Janie

Sorry first function should be VALUE not VAL

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)