Formatting Difficulties: Recommended approach, formula, VBA, etc.?

crayhead

New Member
Joined
Jun 6, 2016
Messages
10
Hi forum, any help on this, including approaches I haven't considered, would be helpful. I scrape several websites to get raw data on foreclosure sales. One such website has changed their formatting and royally hampered my data gathering efforts. My starting sheet contains these data points:
CountySale Date/TimeOpening BidStateSP #File #AddressBook/Page
Person04/08/2019 100000 AM69358.36NC16 SP 13416-12617-FC01500 Virgilina Road Roxboro, North Carolina 27573872/3

<tbody>
</tbody>

Simple enough and was not difficult before. Now, when copying the information directly from the website (displayed horizontally, btw) each individual entry is copied and pasted vertically in this format:
County:
Person
Sale Date:
04/08/201910:00:00 AM
State:
NC
Court SP#:
16 SP 134
Case #:
16-12617-FC01
Address:
500 Virgilina Road Roxboro, NC 27573
Opening Bid Amount:
69538.36
Book Page:
872/3

<tbody>
</tbody>

It's almost as if they are nesting multiple separate tables for each and every entry that they have (the headers get re-copied each time [County, Sale Date, etc.]). I could not ascertain a formula to extract necessary data in adjacent rows and have very limited VBA skills although I love to learn. I essentially labored over it by selecting all blank rows and deleting to compact the data and then used "Find an Replace" function to get rid of all the header info before selecting each entry down to the county data which starts the next entry.

Don't know if I have explained well, appreciate any assistance as there has to be a more efficient way of extracting that data.:(
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Forum statistics

Threads
1,213,556
Messages
6,114,284
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top