Can't sum numbers - Help!

L

lovebaby

I can copy and paste my debit card purchaes from my bank - online.

The numbers are in a column and appear as $14.34 (ex.)- with a dollar sign
in front of them.

I can't sum these numbers. I select them all and convert them to a number
format and they still won't sum.

I f2 the cell and remove the dollar sign and they still won't sum (sum = 0)

Any ideas?
 
K

Ken Wright

Select the range and format the range as numbers and then and do Edit /
Replace, replacing the dollar sign with nothing. If it still won't sum, put
a 1 in an empty cell, copy it, select your data and do edit / Paste Special
/ Tick multiply and values and hit OK.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission :)
------------------------------­------------------------------­----------------
 
L

lovebaby

Still doesn't work. How could they possibly "protect" values such they can't
be summed?
 
S

Sandy Mann

I would think that if you copied then from a website then they will be text
not numbers. Even removing the $ sign and re-fromatting the CELL (not the
contents) as Number or General will not change the fact that they are text.
Try, (on entres with the $ sign removed), copying a previously unused cell
and then highlighting the dtat you want to convert and Paste Special > Add.

I have also read that data copied from the net often has other non-printable
characters attached,usually Character 160, a non breaking space. Try using:

=ISNUMBER(SEARCH(CHAR(160),A6))

and copying down to see if you get a TRUE

I you still have trouble then post back but unless you have a lot of data it
may be easier to clear the data, reformat and manually re-enter the numbers.

--
HTH

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk
 
L

lovebaby

The data spaces. If I manually remove the spaces. if can sum it.

Is there an easy way to remove the spaces?
 
K

Ken Wright

They're not protected they are text, so need converting back to numeric.

Do as i said but before you go there, select the range and do Edit /
Replace, replacing space with nothing.

If this is something you do regularly then it would be well worth having a
copy of Dave McRitchies Trimall macro to hand on a keyboard shortcut:-

Sub TrimALL()
'David McRitchie 2000-07-03 mod 2000-08-16 join.htm
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range
'Also Treat CHR 0160, as a space (CHR 032)
Selection.Replace what:=Chr(160), Replacement:=Chr(32), _
lookat:=xlPart, SearchOrder:=xlByRows,
MatchCase:=False
'Trim in Excel removes extra internal spaces, VBA does not
On Error Resume Next 'in case no text cells in selection
For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants,
xlTextValues))
cell.Value = Application.Trim(cell.Value)
Next cell
On Error GoTo 0
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

I use this all the time

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission :)
------------------------------­------------------------------­----------------
 
S

Sandy Mann

You din't say what type of spaces you have but this formula should work for
either:

=(TRIM(SUBSTITUTE(A6,CHAR(160),)))+0

Use it in an unused column which may be inserted especailly for this reason
and deleted afterwards. Then copy the new numbers and paste back over the
originals using Paste Special > Values

--
HTH

Sandy
[email protected]
Replace@mailinator with @tiscali.co.uk
 
Top