Excel sheet with HTML coding issue

[JBijl]

I am curious if this is even possible… I have been working on solving this for weeks.

I work on a regular basis in an excel sheet that contains a column B that holds text. The text has HTML code in them on several places in the text, some 3 HTML codes and other 5 or 6 HTML codes. See example below:

The example text below displays linked keywords that in the HTML code have coding around the keyword. In the image you can see the full text with the HTML coding. But because this is a posting the HTML coding does not display in full

This text is purely of example <a href="http://www.non-exsisting-url-one.com">purpose</a>. What is written here contains URL’s that have HTML code around them. Within the HTML code is also <a href="http://www.non-exsisting-url-two.com">a keyword</a>. The purpose of this request on MrExcel is to figure out a way to <a href="http://www.non-exsisting-url-three.com">separate</a> every URL and keyword into separate cells. As I work with <a href="http://www.non-exsisting-url-four.com">hundreds of cells</a> with these kind of texts. I would like to find a way to get this into a formula to save time.

The above text would be in cell D3. Cell E3 would then hold just the URL of that particular cell (D3) being: http://www.non-exsisting-url-one.com, and cell F3 would have the keyword in it: purpose

Currently I have to manually input the content of cell E3 and F3 by hand. But as the example already shows, there are more URL’s and keywords. This is a very lengthy process and is very time consuming. I was thinking that there should be a formula that could be made in Excel to have this done automatically.

I have two ways of seeing this formula.

1) A formula that just cut/pastes the Keyword into F3 from E3. So I would manually copy and past from D3 the URL (http://www.non-exsisting-url-one.com”>purpose) without the last URL code ( </a> ) and then have a formula that would cut and paste the keyword into F3 from E3.

2) A series of formula that would cover the cells E => P. This would be most ideal but is a true challenge. It would look something like this:

<a target='_blank' title='ImageShack - Image And Video Hosting' href='http://img688.imageshack.us/i/screenshot20101029at116.png/'><img src='http://img688.imageshack.us/img688/4479/screenshot20101029at116.png' border='0'/></a>

In the example there are only four URL’s with keyword, but I would need the formula to continue to the 6th as this happens a lot.

I am seriously curious if there is a way to do this as I have not been able to get any further than the first 2 URL’s in code. These are:

E3: =MID(D3,FIND("http:",D3),FIND(">",D3,FIND("http:",D3))-FIND("http:",D3)-1)
F3: =MID(D3,FIND(">",D3)+1,FIND("</",D3,FIND(">",D3))-FIND(">",D3)-1)
G3:=MID(D3,FIND("http://",D3,FIND("http://",D3,1)+1),FIND(">",D3,FIND("http://",D3,FIND("http://",D3,1)+1))-FIND("http://",D3,FIND("http://",D3,1)+1)-1)
H3:=MID(D3,(FIND(">",D3,FIND("http://",D3,FIND("http://",D3,1)+2)+2)+1),FIND("</",D3,(FIND(">",D3,FIND("http://",D3,FIND("http://",D3,1)+2)+2)+1)+1)-(FIND(">",D3,FIND("http://",D3,FIND("http://",D3,1)+2)+2)+1))

This way the formula becomes longer and longer. These formula were made together with a friend of mine who has much more knowledge of Excel than me, but he too could not figure this one out.

I am stuck guys and don’t know what to do. Help anybody???

FYI, I am working on a Mac and can therefore not make use of Macro’s ☹…

Thanks,

J

 

[Spiky]

Well, first, you can upgrade to Office 2011, or downgrade to Office 2004, both of which use macros. Because I think a macro is the solution.

 

[JBijl]

Hi Spiky,

Well, the sheets are being used on multiple Mac's and there we would have to do the up- or downgrade on all macs. As the above formula works (just not all the way that I need it) I was hoping that somebody would be able to figure out how to do that.

Thanks anyway... It could be the last solution