custom function to search for multiple instr?

Gingertrees

Well-known Member
Joined
Sep 21, 2009
Messages
697
I'm trying to write a function to test if a cell contains a website. So far I've got:
Code:
Function IsURL(text)

Dim test As Variant
Dim eml As Variant
eml = InStr(text, "@") 'if not present, position = 0
test = InStr(text, ".com")
If eml > 1 Then
IsURL = "email"
ElseIf test > 4 Then
IsURL = "website"
Else: IsURL = "not"

End If
    
End Function
While I'm sure I could write a half-dozen more IF statements - it seems there's a better way.

I also tried a Case statement, though I suck at syntax on those. The attempt below ALWAYS thinks there's a ".com":
Code:
Function IsURL2(text As Variant)
Dim dom As Variant 'domain
If InStr(text, "@") > 1 Then Exit Function

Select Case InStr(text, dom) > 1 'if not present, value = 0. searching for value >1
    Case dom = ".com"
        IsURL2 = "web"
    Case dom = ".net"
        IsURL2 = "site"
    Case Else
        IsURL2 = "not"
End Select
 
End Function
Help????:oops:
 

tigeravatar

Well-known Member
Joined
Aug 12, 2011
Messages
760
does this need to be a UDF? Wouldn't all URL's start with http? so you could use a formula:
=IF(left(A1,4)="http",TRUE,FALSE)

If that doesn't work, can you show a sample of the data you want to run this function on?
 

Gingertrees

Well-known Member
Joined
Sep 21, 2009
Messages
697
That'd be nice, now wouldn't it? Sadly, this is a data mining project. URLs are entered in whatever #$%$-d up way people wanted. I've got from
www.something.com
to
http://another.biz/yellow
to
catsinhats.net

I feel the UDF is the best route, since I have to do other stuff to this as well.
 

tigeravatar

Well-known Member
Joined
Aug 12, 2011
Messages
760
Gingertrees,

Could give something like this a try. Just update strSearchItems with all things you want it to search for, seperate each term with a comma as shown:
Code:
Public Function IsURL(strText As String) As Boolean
    
    Const strSearchItems As String = "http,.com,.net,.org"
    
    Dim arrSearchItems() As String
    Dim arrIndex As Long
    
    IsURL = False
    
    If InStr(strText, "@") > 0 Then Exit Function
    
    arrSearchItems = Split(strSearchItems, ",")
    For arrIndex = LBound(arrSearchItems) To UBound(arrSearchItems)
        If InStr(1, strText, arrSearchItems(arrIndex), vbTextCompare) > 0 Then
            IsURL = True
            Exit Function
        End If
    Next arrIndex
    
End Function
 

Gingertrees

Well-known Member
Joined
Sep 21, 2009
Messages
697
Looks like that might be it! I'll try it tomorrow. Thanks!
 
Last edited:

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,880
Instead of parsing the string, perhaps this:

Code:
Function IsURL(r As Range) As String
On Error Resume Next
IsURL = "NOT"
Dim url As String
url = r.Hyperlinks(1).Address
If Left(url, 4) = "http" Then IsURL = "URL"
If Left(url, 6) = "mailto" Then IsURL = "EMAIL"
End Function
 

tlowry

Well-known Member
Joined
Nov 3, 2011
Messages
1,367
This is way complicate


How about the


  • www
  • "@" in password@domain...
  • ftp://, https://, scp://, ssh://, or telnet://.
  • Every country can have it's own extension ie [FONT=Arial, Helvetica, sans-serif]French Guiana is .gf[/FONT]
And all this keeps changing so ....

Perhaps a better approach is to "Ping" the website, which I don' t know how to do, but I'm working on it.



 

Gingertrees

Well-known Member
Joined
Sep 21, 2009
Messages
697
tigeravatar: for some reason, the function you provided doesn't seem to work right - either everything is not a URL, or everything is. I can't clearly see why. Ideas???
tlowry: yes, it is a complicated problem. If there was any standardization to the way the websites were listed, your method would be an easy fix. But, tis complicated.
 

tigeravatar

Well-known Member
Joined
Aug 12, 2011
Messages
760
Gingertrees,

That can happen when calculation has been set to manual and the formula is copied down. Try pressing F9 to force a calculation and see if the results update.

To set calculation to automatic (in XL2007):
Office button -> Excel Options -> Formulas -> select "Automatic" for Workbook Calculation -> OK
 

Forum statistics

Threads
1,081,622
Messages
5,360,079
Members
400,569
Latest member
tcormack

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top