Extracting multiple URLs from cell containing URLs plus text

[lmcmanus1]

Hi there,

I'm trying to split all URLs within a cell - the cell contains a mixture of text and different URLs - and extract them to adjacent cells.

I have been able to separate single URLs to an adjacent cell, but only up to the first instance of a unique URL - I'm just not sure how to create a formula which finds the next URL to separate from the text and place in a new cell.

An example initial cell (A1) contains:

***
Google is an online search platform, which can be found at www.google.com whereas facebook is a social media platform, which can be found at www.facebook.com most of the time.
***

I want to extract both URLs into separate adjacent cells (B1 and C1)
- Where B1 shows: "www.google.com"
- Where C1 shows: "www.facebook.com"

Failing this I would be happy if I could extract all URLs from a cell (A1) into a single cell (B1)

I hope this all makes sense!

Thanks in advance.

 

[Roderick_E]

Think about what is common to the URLS. Will they always begin with www. or do you anticipate a case when it might say http:// or just google.com?
Anyhow, if you always expect www. as the lead, you can turn the entire string into an array then loop through the array looking for www. or maybe even . and extract those strings only.

 

[lmcmanus1]

Yep, I have separate columns for variations of links to cover the http issue.

With regards to setting up my string as an array to then loop through - is there a straightforward way of doing this? Is it by using VBA?

 

[István Hirsch]

If it is always www.....

Give this a try:

Excel Workbook
	A	B	C
1	Google is an online search platform, which can be found at Google whereas facebook is a social media platform, which can be found at www.facebook.com most of the time.	www.google.com	www.facebook.com
Sheet

 

[Rick Rothstein]

As long as your URL's are surrounded by spaces, then this UDF appears to work (even if the URL is preceded by "http//:" or starts with something other than "www")...

[table="******* 500"]
[tr]
	[td]Function URLs(Txt As String, Index As Long) As Variant
  Dim X As Long, DotCount As Long
  Dim Pat As String, EndPat As String, Parts() As String
  Pat = "*[!&-;?-[_a-z~=!" & Chr$(1) & "]."
  EndPat = "[!&-;?-[_a-z~=!" & Chr$(1) & "]*"
  Parts = Split(Txt)
  For X = 0 To UBound(Parts)
    If Parts(X) Like "*?.?*" Then
      DotCount = Len(Parts(X)) - Len(Replace(Parts(X), ".", ""))
      If " " & Replace(Parts(X), "]", Chr$(1)) & " " Like Application.Rept(Pat, DotCount) & EndPat Then Parts(X) = ""
    Else
      Parts(X) = ""
    End If
  Next
  On Error GoTo IndexTooBig
  URLs = Split(Application.Trim(Join(Parts)))(Index - 1)
  Exit Function
IndexTooBig:
  URLs = ""
End Function[/td]
[/tr]
[/table]

The function takes two arguments... the text to process (can be a quoted text string or a cell reference for a cell containing the text) and an index to the URL wanted. For example, in the text sample you posted in your original message, you could directly retrieve the second URL in the text using this formula...

=URLs(A1,2)

Since you will want all of the URLs for the text in, say, cell A1, you would do it by putting this formula in B1 and copying it across for as many cells as you think you would ever have URLs to fill them (note the absolute cell references... they are important)...

=URLs($A1,COLUMNS($B:B))


HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use NameOfTheUDF just like it was a built-in Excel function using formulas similar to those shown above.

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[lmcmanus1]

Istvan - this has worked wonderfully, thanks!

Rick - this also looks extremely useful - I haven't put it into practice yet but will let you know if I have any issues.

Many thanks again for all your help.

 

[István Hirsch]

Istvan - this has worked wonderfully, thanks!

Rick - this also looks extremely useful - I haven't put it into practice yet but will let you know if I have any issues.

Many thanks again for all your help.

Thanks for the feedback.

Note: if the URL in the string ends with a comma or a period, the extracted URL will also have a comma or period at the end. If it is something to be deleted, wrap the formula in substitution function(s), for example: =SUBSTITUTE(formula,".","")