HTML Table pasted to Excel

J

Josh O.

I have to copy a table out of a web page and paste into excel and then add
formulas to the spreadsheet.

When I try to sum a column of figures, I get a "0" result because the
numbers have a "$" before the number and a space after the number.

Is there any way to either sum them as they are, or remove the $ and space
in all the cells?

Example (each cell has a space after the last number):
$300.00
$126.67
$269.17
$180.00
 
R

Roger Govier

Hi

One way, assuming your data is in column A, enter this formula in B1
=--MID(A1,2,len(a1)-1)
Copy down the column

Regards

Roger Govier
 
D

David McRitchie

Hi Josh,

You can check the formatting that you currently have under
format, cells, number (tab), custom
then modify that so that the third operand of the format is empty

Change
General
to
General; -General;;@
---- note the two semicolons to denote omitted parameter for zero


change:
_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)
to
_($* #,##0.00_);_($* (#,##0.00);;_(@_)


Some examples of formatting, which may or may not relate to your question:
.. http://www.mvps.org/dmcritchie/excel/forula.htm
 
Top