How to Paste into Excel and Make True Numbers

D

doyle60

I have a query that I paste into Excel. But excel doesn't seem to
know they are numbers. I format them to numbers in Excel but need to
double click each cell to have it *really* change to a number. In
other words, when I Sum() the column in Excel, I get no reply. But if
I double click one of the fields in that Sum() range, the Sum() starts
to work.

The trouble is I don't want to double click 250,000 cells!

What's at issue here? My Access query chain is quite complex and I
don't want to get into it and find what I did wrong.

Can I do something easy in Excel to make it understand they are all
numbers?

Can't find answer on the net.

Thanks,

Matt
 
G

Graham Mandeno

Hi Matt

As long as the query field is numeric, the data should paste into excel as a
number. However, if it is a complex calculated field, particularly one
involving an IIf or Nz function, then it could be interpreted as text.

I suggest you force the field to be numeric with a CLng/CDbl/CCur (whichever
is appropriate). If you are still having trouble then post back with the
actual expression that is being used to calculate the field.
 
D

doyle60

Yes, it happens because I use a union query that puts two different
things in the same column, percents and numbers, and I format each
differently.

I can fix it by just doing the numbers and adding the percents some
other way.

But isn't there a way to do something in Excel when this problem
occurs? It happens to me all the time. Sometimes, for example, I
have a date in Excel that is 04/16/07 and I want to change it to
4/16/07 format. So I change the format of the whole column and nothing
happens. But when I double click on the cell, presto, it changes to
the form I want. Why? Why does this happen? I have to then double
click a hundred cells to get it to format correctly.

Sometimes I'm not the creator of the Excel spreadsheet so there is
nothing I can do.

I believe there are a hundreds of people across the world right now
double clicking cell after cell to get Excel to format correctly. I
have 1997 version, I believe. Is this issue fixed yet?

Is it only me or do others know what I'm talking about?

Thanks,

Matt
 
G

Graham Mandeno

Hi Matt

I'm sorry - I'm not an Excel expert and I don't have a definite answer.

One thing you could try is selecting the column (in Excel) then do a copy
and "Paste special", and specify "Values only".

If that doesn't work then maybe you can get some help from one of the Excel
newsgroups.
 
Top