CODEWITHSUNDEEP
vbaexcelexcel-formula
Ade Thompson
Ade Thompson

Reputation: 33

Find URL in cell/string with Excel VBA

I have a cell/string that contains a varierty of information including RAW http:// links.

I'm wanting find a way to extract these RAW URLS and convert them into HTML links ( etc not clickable links in Excel). see below for an example:

Source: This is a sample link: http://www.bbc.co.uk Please visit.
Output: This is a sample link: <a href="http://www.bbc.co.uk">http://www.bbc.co.uk</a> Please visit.

I've dabbled with replace function but this doesn't seem to be giving me the required result.

Any help appreciated.

Upvotes: 3

Views: 1782

Answers (3)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60174

Here is a UDF that will replace. And should also work with multiple URL's in the same string. IT uses the Regular Expression engine to do the work, and will find URL's with various protocols (eg http, https, ftp, File)

Also, if the link is part of a sentence, the punctuation at the end (eg period, comma, etc) will not be included within the html tags.

Option Explicit
Function LinkToHTML(S As String) As String
    Dim RE As Object
    Const sPat As String = "\b(https?|ftps?|file)://[-A-Z0-9+&@#/%?=~_|$!:,.;]*[A-Z0-9+&@#/%=~_|$]"
    Const sRepl As String = "<a href=""$&"">$&</a>"

Set RE = CreateObject("vbscript.regexp")
With RE
    .Global = True
    .Pattern = sPat
    .ignorecase = True
    LinkToHTML = .Replace(S, sRepl)
End With

End Function

enter image description here

Upvotes: 1

user4039065
user4039065

Reputation:

Oh-key-doh-key... so you are '... looking for pointers as to where to start.'. Here is a start:

=SUBSTITUTE(REPLACE(A2, FIND(" ", A2, FIND("http", A2)), 1, """>"), " http", "<a href=""http")&"</a>"

Now you can refine that using the same functions and methods to strip out the URL into the anchor element.

enter image description here

Upvotes: 1

R3uK
R3uK

Reputation: 14537

If your input is in A1, place this in A2 to get the cleaned link :

=TRIM(SUBSTITUTE(SUBSTITUTE(A1,"This is a sample link: ","")," Please visit.",""))

Using cleaned link in A2 :

="This is a sample link: <a href="""&A2&""">"&A2&"</a> Please visit."

Or both combined (input in A1) :

="This is a sample link: <a href="""&TRIM(SUBSTITUTE(SUBSTITUTE(A1,"This is a sample link: ","")," Please visit.",""))&""">"&TRIM(SUBSTITUTE(SUBSTITUTE(A1,"This is a sample link: ","")," Please visit.",""))&"</a> Please visit."

Upvotes: 1

Related Questions