custom function to search for multiple instr?

[Gingertrees]

I'm trying to write a function to test if a cell contains a website. So far I've got:

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":

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????

 

[tigeravatar]

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]

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]

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:

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]

Looks like that might be it! I'll try it tomorrow. Thanks!

 

[Scott Huish]

Instead of parsing the string, perhaps this:

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]

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]

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]

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