How to Paste into Excel and Make True Numbers

  • Thread starter Thread starter doyle60
  • Start date Start date
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
 
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.
 
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
 
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.
 
Back
Top