# 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()

#1
January 31, 2012 at 15:21:25
 Have you tried the =VALUE() function?Try this: =VALUE(TRIM(CLEAN(A1)))It will clean out all the stray space characters and any others they may be lurking in the cell.MIKEhttp://www.skeptic.com/

Report •

#2
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:'234'234=SUM(A1:A2) returned 0.I F9'd this formula and got what you got:=SUM({"234";"234"})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 Next End Sub ```Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

#3
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