1

I have an Excel workbook, in column A each cell contains all URLs of a site (this means hundreds of URLs in one cell).

Among all the URLs within a single cell I want to extract only the URL that contains the word contact

For example:

Cell A1: http://examplesite.com/index.html; http://examplesite.com/login.php; http://examplesite.com/contact.html; http://examplesite.com/about.html

Cell A2: http://hello.publish.com/alumni; http://hello.publish.com/contact-us; http://hello.publish.com/sitemap; http://hellopublish.com/projects

And so on

I woluld like to have this output on Column B

Cell B1: http://examplesite.com/contact.html

Cell B2: http://hello.publish.com/contact-us

1

Try the following User Defined Function (UDF):

Public Function FindContact(inpt As String) As String
   ary = Split(inpt, "; ")
   For Each a In ary
      If InStr(1, a, "contact") > 0 Then
         FindContact = a
         Exit Function
      End If
   Next a
   FindContact = ""
End Function

enter image description here

User Defined Functions (UDFs) are very easy to install and use:

  1. ALT-F11 brings up the VBE window
  2. ALT-I ALT-M opens a fresh module
  3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the UDF:

  1. bring up the VBE window as above
  2. clear the code out
  3. close the VBE window

To use the UDF from Excel:

=FindContact(A1)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

and for specifics on UDFs, see:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

Macros must be enabled for this to work!

  • UDF above works perfectly! Thankyou so very much. – Tmr Sep 16 '15 at 13:27
-1

Since all of your data is going down vertically, the command for you is the VLookup command. First in the B column set up an identifier for each of the URL starting from 1 and going on until the end. Do this by dragging the black dot at the corner all the way down until the end URL is met. example: VLookup("contact",$A1:$B13,2,TRUE).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.