weird! Excel refuses to compute numbers...

  • Thread starter Thread starter LunaMoon
  • Start date Start date
L

LunaMoon

Hi all,

I copied and pasted the HTML table into Excel.

http://finance.google.com/finance?fstype=ii&q=NASDAQ:FMCN

And when I do "=B11/B10" in Excel,

it complains "#Value!"

I have checked that B11 and B10 are numbers, but why haven't they been
treated as numbers?

And I've tried manipulating other numbers in the sheet (or at least
they are numbers but in a text format, I guess), they all fail ...
 
Lots of times, when you copy|paste from a web site, you'll get those HTML
non-breaking spaces (char(160)).

You can clean them up with formulas, but if you have to do it lots of times (or
with lots of data), you may want to use a macro.

David McRitchie has a macro that can help clean this:
http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()")

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)
 
If you have pasted a table from an HTML source then you probably have
non-breaking spaces (character code 160) in among the data. Highlight
all the data and CTRL-H (Find & Replace):

Find What: ALT-0160
Replace With: leave blank
Click Replace All

where ALT-0160 means hold the ALT key down while you type 0160 on the
numeric keypad.

Hope this helps.

Pete
 
Back
Top