copy and paste data from website

B

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.
 
N

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.
|
|
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Remove numbers from brackets 0
How to paste this down? 3
Paste Link and Link Text From Browser to Word 0
Copy and Paste Word 2010 0
Copy addition formula of non-consecutive horizontal cells 2
Add additional information to excel form after original data entry 0
Pasting a cell into bookmarked table from excel vba 1
Copy and paste slide from one presentation to another presentation gives an error 2

Top