Formatting numbers that aren't recognized

  • Thread starter Thread starter noah
  • Start date Start date
N

noah

I've had a problem for a while now.
I copy data from another source and paste into a workbook everyday.
have a column that's filled with just numbers. Whenever I try to do a
autosum function or manually type "=Sum(A1-A150)" I don't get a result
I just see the formula in the cell- no error.
Or sometimes I do, but the result is (for example: total= 2, when i
should be 50.) It recognized some of the cells.
Also whenever I try to change the formula of the cells, it doesn'
effect the number within the cell.
It appears that whatever I do; the number in the cell is visable to m
but is not recognized by excel.

How can I fix this? Or prevent it from happening?
I think that when the cells are formated "text" before I paste th
data, it works. It also works if I manually type the number in over th
original. But I can't go back over thousands of cells manually retypin
them. I need a solution to fix the format and have excel recognize th
data.

I copy my data from another excel based software program, but I can'
tell in what format it originates.
Please Help.:confused
 
Does the same thing happen when you paste the data as values, having already
formatted cells as number?

You may also try multiplying the range by 1, by entering 1 in a cell, copy
it, select your range of numbers, past special>multiply.
 
Hi
try the following:
- set the format for these cells to 'General'
- copy an empty cell (e.g. with CTRL+C)
- select your cells with values
- goto 'Edit - Paste Special' and choose the action 'Add'
 
Hi Noah
If the data is pasting in as text, and they are all numbers, you can use the following array entered formula

=SUM(VALUE(A1:A4)

Enter this with ctrl-shift-enter, instead of enter, the formula should appear with { } around it. This will convert the data into numbers and then sum them up. If you have any real text in the range then this won't work, but it might be a quick solution to avoid converting the data

Good Luck
Mark Graesse
(e-mail address removed)
Boston M

----- noah > wrote: ----

I've had a problem for a while now
I copy data from another source and paste into a workbook everyday.
have a column that's filled with just numbers. Whenever I try to do a
autosum function or manually type "=Sum(A1-A150)" I don't get a result
I just see the formula in the cell- no error
Or sometimes I do, but the result is (for example: total= 2, when i
should be 50.) It recognized some of the cells
Also whenever I try to change the formula of the cells, it doesn'
effect the number within the cell.
It appears that whatever I do; the number in the cell is visable to m
but is not recognized by excel.

How can I fix this? Or prevent it from happening
I think that when the cells are formated "text" before I paste th
data, it works. It also works if I manually type the number in over th
original. But I can't go back over thousands of cells manually retypin
them. I need a solution to fix the format and have excel recognize th
data

I copy my data from another excel based software program, but I can'
tell in what format it originates
Please Help.:confused
 
Back
Top