Is it possible to extract url from hyperlinks in excel if it's setup like this?

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.

john smith

Posted 2016-08-09T19:36:26.190

Reputation: 3

Answers

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.

user555689

Posted 2016-08-09T19:36:26.190

Reputation:

Cool. Now I am kind of a noob at Excel. How do I apply that formula to all the links? – john smith – 2016-08-09T20:56:26.513

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 – None – 2016-08-10T18:34:58.977

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 – 2016-08-10T21:44:30.507

is your links in one cell only – None – 2016-08-11T17:14:47.943

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 – 2016-08-11T22:41:22.500

I copied your example and paste it in A1 and used the formula in B1 and got: **http://mywebsite.com/link?id=SsQD2GsCjgY&offerid=445126&mid-heel-sandal-black** – None – 2016-08-12T19:42:30.617

Try writing the formula with fx not copy paste maybe your settings are different – None – 2016-08-12T19:49:58.307

Ah yes. writing the formula fixed the problem. Thanks a bunch! – john smith – 2016-08-17T22:15:11.763

Actually I found out what I was doing wrong. After copying the formula in the formula field, I had to press ENTER for it to work. – john smith – 2016-08-17T22:16:42.840

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

user186658

Posted 2016-08-09T19:36:26.190

Reputation: 127