Copy, Paste - Formatting Error

Hi friends,
I am facing a peculiar problem in formatting a text table copied from a website. Normally if we copy a table and paste it in excel then the formatting will be intact. But here in my case the copied text will be in plain text format without formatting. If it was just a single table then would have done it manually. There are more than 1300 products there and copy, paste, edit manually in excel is really time consuming.

About the method I followed :
First I copy the text from table and paste it in excel. A single Column will be filled with entire data in several rows. Then I cut and paste the values to make it a proper table. When we cut and paste the data, the empty rows will be there which should be deleted manually. Later the sub-headings will be merged to a single cell. Apply Borders for every cell will result in a proper Table format.

Is there any easiest way to do this task with Macros?

P.S.: I am new to this forum so Images and Links to websites will not allowed.

Thanks in advance

Attachment 785847

Attachment 785848

Trying to attach images and link to reference website.

Attachment 785850

Attachment 785851

Attachment 785852

Final table is like the above image.

I can't get any of your attachments to work, but if you post an example of what your sheet looks like BEFORE you do any manual formatting (i.e. what it looks like after you paste your text into excel) I'll have a look at it for you. See yellow banner for instructions on how to attach your workbook.

Thank you Croweater for the reply. I have attached an excel file for getting a clear idea. In Sheet1 I have pasted the contents from the webpage table. Sheet2 is having the values to be formatted likewise. Sheet3 is having a final table. There may be more or less data in each table and the contents are changing from table to table.

Try this Macro in Sheet1. It will clear Sheet3 and Create your table from the detail in Sheet1. Let me know how it goes.

Please Login or Register  to view this content.