#VALUE on data copied from web page

[M Skabialka]

I copied some data about my online account from a web-page into Excel 07,
but when I try to write a formula referring to one of the cells that shows a
currency amount I get #VALUE. If I look at "show calculation steps" it
shows the money amount in quotes. I have changed the format to currency for
this currency column, and even tried to copy and paste back just the values,
but the formulas won't work. Short of retyping in all of the values (about
100 rows) how can I convert them to actual numbers so the formulae will
work?

 

[Gord Dibben]

Format all cells to General.

Copy an empty cell.

Select the range of bogus numbers and paste special>add>ok>esc


Gord Dibben MS Excel MVP

 

[M Skabialka]

Good grief - this worked - what an odd thing to have to try - not intuitive
at all. Thanks!

 

[Gord Dibben]

Thanks for the feedback.

Copying data from a webpage can bring the numbers in as text.

The paste special coerces Excel to recognize the text as numbers.

You were lucky in this case.

Sometimes data from the web can contain non-breaking spaces which do not
respond to the paste special method.

You would have to do an edit>replace for that nbsp

Replace what: Alt + 0160

Replace with: nothing


Gord

 

[M Skabialka]

I will try this also and ad it to my box of tools.
Thanks again