Extract text strings

I have a very large spreadsheet with html in each cell in column 1. There are multiple text strings (potentially 3 to 20) within the html in each cell. Each string is preceded with HREF= and the string has a " before and after the string.

I want to have a macro that searches each cell in column 1 and finds the HREF= (multiple instances...as few as 3 to as many as 20) and extracts the string between the double quotes (i.e. "string") and writes them to the first column in worksheet 2 in the workbook.

I am not a programmer...but have spent a few days trying to find a macro or use the MID or FIND function to do this with minimal success. Can someone please help?

Thanks!

Please Login or Register  to view this content.

Paul....Thanks for the code. It did it flawlessly! I really appreciate the help!

Hey Paul, quick followup question: One variable I had not included.....in my source worksheet1 column (b) has the sourcepage url for the html in column (A) (could be different source url for each cell). Is there a quick addition to the code that will write this value to the column next to each href link from that cell in worksheet2? This would be a huge help in extracting the data....

Thanks!

Here is some "sample" data in the same format to test on......Thanks!

You may notice that in each raw html cell there are typically duplicates of each href link....and in cells from the same source URL in column 2, I have to manually remove duplicates. So typically we have to take the output file (which may be thousands of lines long) and sort by column 2, then take that html and remove the href links/remove duplicates for all the href links from the same source url, then copy the source url to the final list from that link and put it in the final worksheet for analysis. It would save a multiple steps if it removed duplicates where column 2 is the same as well....not sure if that is possible but thought I would ask. Maybe it is a separate script I run on the results where it looks for duplicates where they have the same source url in column 2......Thanks!

I can't detect a connection between herefs in column A and URL in column B.

Based on your example: all the unique hreff in column A
from cell A20 downward.

Please Login or Register  to view this content.

Hey Paul, tried your revised code....worked perfectly the first time.....Thanks again!