copy and paste data from website

[Brian]

I need to copy and paste large quantity of text and numerical data
(currency) from a website into an excel worksheet. When I paste the data
into the excel worksheet, the columns have leading spaces and the column
with the numerical data has leading spaces so it is not recognized at a
number and will not calculate when I add a formula to add the sum of the
column IE: =SUM(B5:I5). I have to manually go into each cell and remove the
leading spaces and the $ from the number of each cell and then the formula
will sum the totals of the referenced cells. It is like the value in the
cells $45.61 etc.. are not recognized as a number until I remove the
leading spaces and the dollar sign. The data that I ap copying from the
website is center justified so the formatting from the website is causing
problems when I paste.

 

[Niek Otten]

Hi Brian

You don't have to edit manually. Use the TRIM, CLEAN and SUBSITUTE functions to clean the data automatically.
There may be non-breaking spaces in the numbers: they can be removed with David McRitchie's TRIMALL function:
http://www.mvps.org/dmcritchie/excel/join.htm#trimall

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I need to copy and paste large quantity of text and numerical data
| (currency) from a website into an excel worksheet. When I paste the data
| into the excel worksheet, the columns have leading spaces and the column
| with the numerical data has leading spaces so it is not recognized at a
| number and will not calculate when I add a formula to add the sum of the
| column IE: =SUM(B5:I5). I have to manually go into each cell and remove the
| leading spaces and the $ from the number of each cell and then the formula
| will sum the totals of the referenced cells. It is like the value in the
| cells $45.61 etc.. are not recognized as a number until I remove the
| leading spaces and the dollar sign. The data that I ap copying from the
| website is center justified so the formatting from the website is causing
| problems when I paste.
|
|