Can't sum numbers - Help!

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?

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 :-)
------------------------------*------------------------------*----------------

"lovebaby" <[email protected]> wrote in message
news:[email protected]...
>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?
>

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

"Ken Wright" <[email protected]> wrote in message
news:[email protected]...
> 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 :-)
> ------------------------------*------------------------------*----------------
>
> "lovebaby" <[email protected]> wrote in message
> news:[email protected]...
>>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?
>>
>
>

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

"lovebaby" <[email protected]> wrote in message
news:[email protected]...
>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?
>

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

Is there an easy way to remove the spaces?
"Sandy Mann" <[email protected]> wrote in message
news:[email protected]...
>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
>
> "lovebaby" <[email protected]> wrote in message
> news:[email protected]...
>>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?
>>
>
>

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 :-)
------------------------------*------------------------------*----------------

"lovebaby" <[email protected]> wrote in message
news:[email protected]...
> Still doesn't work. How could they possibly "protect" values such they
> can't be summed?
>
> "Ken Wright" <[email protected]> wrote in message
> news:[email protected]...
>> 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 :-)
>> ------------------------------*------------------------------*----------------
>>
>> "lovebaby" <[email protected]> wrote in message
>> news:[email protected]...
>>>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?
>>>
>>
>>
>
>

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


"lovebaby" <[email protected]> wrote in message
news:[email protected]...
> The data spaces. If I manually remove the spaces. if can sum it.
>
> Is there an easy way to remove the spaces?
> "Sandy Mann" <[email protected]> wrote in message
> news:[email protected]...
>>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
>>
>> "lovebaby" <[email protected]> wrote in message
>> news:[email protected]...
>>>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?
>>>
>>
>>
>
>

Simplest plan ...add asci character 160 to blank cell (insert menus), copy in 'find and replace' with nothing ...Pete