3

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.

Improve this question
3
  • 1
    Welcome to SO, please take the tour and show us what you tried (edit your post to include your formula)! ;) Commented Mar 15, 2017 at 12:31
  • Unfortunately I don't have any working code for the above issue. Looking for pointers as to where to start. Commented Mar 15, 2017 at 12:38
  • 1
    Ok, but as you can see, there is no need for code! ;) But you should definitely take the tour (click on "tour", it's blue so it's a link! ;) ) Commented Mar 15, 2017 at 12:42

3 Answers 3

Reset to default
1

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

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Ron that works perfectly, just what I can looking for. Many thanks.
This works great within a cell but cant get it to perform the same result when calling it from within VBA. Output = "This is a test link http://www.bbc.co.uk" LinkToHtml (Output)
@AdeThompson It works fine here, either when called from a cell or from within a VBA routine. Your output indicates the Regex procedure is not being called, or does not recognize the URL as such.
@AdeThompson If the line in your comment represents two lines of VBA code, then you are not doing anything with the output from the formula. You need to assign it to something.
Yeah thanks worked that out in the end. Thought it would apply to the variable but didn't. Thanks again.
|
1

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."
Improve this answer

3 Comments

The cells/string content will vary so can't search for specific strings. The only thing thats constant will be the url which should start with http:// or just www.
@AdeThompson : You could have say so in your question... But first, I insist that you take the tour
Maybe I wasnt clear but I thought 'contains a variety of information' was enough. I've been through the tour.... Twice.
1

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

Improve this answer

3 Comments

this seems to just give me VALUE!
Ok I resolved that, wasnt including the text after the URL. This seems to be getting me closer to the solution. Just need to work out how to make it do it if it finds multiple insances of URL's. Oh and also put the URL with the <a href> tag. Thanks for the help.
The formula above shows how to find the begnning and the end of the URL, use those and a little maths with the MID function to pull out the URL into the existing REPLACE function's replacement text.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.