0

I have few hundred lines like this in excel file. I have tried to copy them one by one and it's very tiresome; so I am wondering if there is a away to extract the urls from all of them at once?

<a href="http://mywebsite.com/link?id=SsQD2GsCjgY&offerid=445126&mid-heel-sandal-black"><IMG border=0 src="https://e31vejvoh8fjtd.bi/catalog/product/1/0/1039-000064-2412-2.jpg" ></a><IMG border=0 width=1 height=1 src="http://ad.fjuyla.com/fs-bin/show?id=SsQD2GsCjgY&bids=445126.3671510716&type=2&subid=0" >                  

Note*: I mean the main href url, not the src url.

1

You can use the following Formula if all URLs look the same:
=MID(A1,FIND("=",A1,1)+2,FIND(">",A1,1)-FIND("=",A1,1)-3)
It extract the middle text between the first = and the first >
A1 is the reference where your data is located
The first Find will give the place of = + 2 where the h of http starts
The second Find will give the place -1 of > end of the URL
The difference between the two find for the length of the URL
and you can copy this formula.

enter image description here

  • Cool. Now I am kind of a noob at Excel. How do I apply that formula to all the links? – john smith Aug 9 '16 at 20:56
  • Write or copy your links to column A and write this formula in column B and copy it down in front of the links you will have the result in column B – user555689 Aug 10 '16 at 18:34
  • I have all the links in Column A and the formula in Column B. I dragged it down so that there is a formula for each link row. Nothing happens. No results show up in Column B. – john smith Aug 10 '16 at 21:44
  • is your links in one cell only – user555689 Aug 11 '16 at 17:14
  • All links are in separate cells. So for eg. 20 links in 20 cells with Column A with href links and Column B with the formulas. Does it work on your Excel? – john smith Aug 11 '16 at 22:41
0

Assuming the lines all contain

<a href=" ...... "><IMG

You could use regex to filter by that. For how to use regex in excel, look here: https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.