custom function to search for multiple instr?

Gingertrees

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

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.



 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

WildBurrow
  • Solved
Userform write to multiple textboxes on specific pages
Replies
2
Views
189
WildBurrow
WildBurrow
J
  • Question Question
How can I copy the grouping rules from one pivot table to another pivot table?
Replies
0
Views
249
jaza_tom
J
T
  • Question Question
Custom ribbon Delete button status at runtime
Replies
6
Views
261
tiredofit
T
K
  • Question Question
Reverse Engineer Outlook Macro since New Outlook does not allow Macros
Replies
1
Views
346
John_w
J
I
  • Question Question
Delete customers row of values after my code has completed moving its copy
2
Replies
17
Views
514
ipbr21054
I

Forum statistics

Threads
1,223,246
Messages
6,170,996
Members
452,373
Latest member
TimReeks

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top