Numbers as text when copied from ACCESS

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

Guest

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!
 
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)
 
Janie

Sorry first function should be VALUE not VAL

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
 
Nick said:
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
...
Sorry first function should be VALUE not VAL
Click to expand...

I think you got it right first time in i.e. it should be VAL.
(another) Jamie.

--
 
Back
Top