Status Bar: SUM function doesn't work for numbers copied from web

[suriogl]

I copied some numbers from my account statement which were in the form
-$50.25 etc. to a spreadsheet, took off the '-$' part and tried to get a SUM
of those numbers in the status bar. The status bar SUM function doesn't seem
to recognize these numbers. I type some numbers and they are recognised. I
read somewhere that numbers could be stored as text. I tried converting to
numbers but it didn't work. I also don't see the diamond icon when the cell
is highlighted which helps to convert the text to numbers.
What could be the problem. Please suggest.

 

[FSt1]

hi
if you copied from the net, they probably are text.
type a 1 in any cell then copy it.
highlight your numbers then pastespecial multiply.
that should convert your text number to plain numbers.

Regards
FSt1

 

[suriogl]

Thanks FSt1, I tried that earlier but it didn't work. However i spent some
time today and observed that the numbers copied from the web page was adding
some thing in the beginning and end. It appears to be blank so don't the
exact character. So i used 'Find and Replace' and copied the character from
the number to be replace with nothing. And it worked. All the numbers got
converted from ' -$145.00 ' to ($145.00) and the color changed to RED. I
could see the SUM of the numbers in the status bar.
So i don't know what that mysterious character was in the number copied from
the web page.

 

[Pete_UK]

When you import data from a website (in particular) you often get the
non-breaking space character included - that has a code of 160 instead of
the normal space character code of 32. So in future you can highlight all
the cells and do CTRL-H (Find/Replace), and:

Find what: Alt-0160
Replace with: leave blank
then click Replace All

where Alt-0160 means to hold down the Alt key and type 0160 on the numeric
keypad.

Hope this helps.

Pete

 

[Gord Dibben]

Usually a non-breaking space.........ascii 160


Gord Dibben MS Excel MVP