Excel 2010: Column of numbers returns 0 when =sum()

January 31, 2012 at 14:50:10
Specs: Windows 7
ok, so I have read a whole load of articles and I am fairly sure that the numbers are stored as text. However I have tried virtually every recommendation to get them to turn to numbers and nothing has worked.

when i select the range in the formula and hit f9 all the numbers are in "234" quotes so I understand that this proves they are stored as text.

- have tried multiplying all by '1' stored in a seperate cell.
- have tried setting to general, copying, paste special values and add.
- have checked tools options calculations are set to automatic not manual.
- I have tried date->text to columns, finish
- There looks like there are trailing spaces, I have removed those but doesn't help. It just takes the decimal places away so now C2 is 39. rather than 39.00
- All numbers are left justified, reinforcing view that they are stored as text.

See More: Excel 2010: Column of numbers returns 0 when =sum()

January 31, 2012 at 15:21:25
Have you tried the =VALUE() function?

Try this:


It will clean out all the stray space characters and any others they may be lurking in the cell.



Report •

January 31, 2012 at 15:34:36
I don't know if this will help, but I put these numbers in A1:A2 as text by putting a single quote in front of them:


=SUM(A1:A2) returned 0.

I F9'd this formula and got what you got:


I then ran this code against A1:A2 and it converted them to numbers:

Sub ConvertToNumbers()
  For rw = 1 To 2
     If Not WorksheetFunction.IsNumber(Cells(rw, 1)) Then
        Cells(rw, 1) = Cells(rw, 1) * 1
     End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

February 1, 2012 at 02:52:27
I managed to resolve it using text to columns and taking out tab space delimiters at the front of the numbers.

This then converted them to numbers.

Thanks for the responses!

Report •
Related Solutions

Ask Question