HTML Table pasted to Excel
 

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

HTML Table pasted to Excel
 

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


Josh O. wrote:
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

HTML Table pasted to Excel
 

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
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Roger Govier" wrote in message ...
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


Josh O. wrote:
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