How to identify website address in a sentence

[robertexcel]

I was searching for some excel help online and have noticed this amazing forum. I hope even I will get some support here!

OK, here is the question (Tough one for me).. I have around 1000 sentences (one sentence in one row) in say column A, all these sentences have website addresses included. For E.g. in A1 the sentence is "there is a website address in this sentence and I am searching for some solution in mrexcel.com and I hope I get some reply as I have searched in www.google.com".

Notice that a website address may not start with 'WWW'. Now, is it possible in excel using formulas or some macro, to get mrexcel.com in column "B1" and www.google.com in column "C1".

You can assume that there will not be more than 10 website addresses in one sentence and also it is not necessary that a website should end with ".com". Finally, the # of rows can be a variable, as I get these sentences on a daily basis, in general # of rows will not exceed a million (I hope this is not a big number for all the programming geeks). I tried a bit on excel using formulas but I am not able to do it, can someone pleased guide me in this regard. Thanks in advance and hope I get some gr8 reply!

 

[Michael M]

My logic is based on the fact that two sentences would mean there would be a space after the period(.), thereby not qualifying it as a web address.

The macro will, I believe need to use the InStr function to exclude any cell that has a period(.)
All other cells could then be deleted and the remaining cells shifted to the left.
I have an example somewhere of something like this, but it's at the office and I don't thin k I'll be there until Monday.....but if I find a sample I'll post back

 

[diddi]

hey MM
this is like a red rag to a bull

Sub GetWebAddresses()
    Dim txt As String, Pos As Long, Row As Long
    Dim offst As Long, offst2 As Long
    For Row = 1 To 20        ' change to last row in A
        txt = Cells(Row, 1).Value
        While txt <> ""
            Pos = InStr(1, txt, ".")
            If Pos > 1 And Pos <> Len(txt) Then
                offst = 1
                While Mid(txt, Pos - offst, 1) <> " " And Pos - offst <> 1
                    offst = offst + 1
                Wend
                offst2 = 1
                While Mid(txt, Pos + offst2, 1) <> " " And Pos + offst2 <> Len(txt) + 1
                    offst2 = offst2 + 1
                Wend
                If offst2 > 1 Then MsgBox ("Found one in row number " & Row _
                    & "." & Chr(13) & Mid(txt, Pos - offst, offst2 + offst))
                txt = Right(txt, Len(txt) - Pos - offst2 + 1)
            Else
                txt = ""
            End If
        Wend
    Next Row
End Sub

 

[Michael M]

Hi P
Nice code....but only indentifies the domain name with a MsgBox.
The OP wanted the domain name stripped out to column "B"

Robert, try this...but the best I can do in the case of multiple domains in the one cell is putting them in columns "B", then "C"...etc.

Also, there may be a little housekeeping to be done when completed, some names might be picked up as domains if there isn't a space after the period, etc.....may be diddi can improve on my effort.

Sub Stripper() 'stripe domain names from text string
Dim lr As Long, r As Long, LC As Integer, c As Integer
lr = Cells(Rows.Count, "A").End(xlUp).Row
        Range("A2:A" & lr).TextToColumns Destination:=Range("A2"), DataType:=xlDelimited, _
        ConsecutiveDelimiter:=True, Space:=True
    If WorksheetFunction.CountA(Cells) > 0 Then
        LC = Cells.Find(What:="*", After:=[A1], _
        SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    End If
For c = LC To 1 Step -1
            lr = Cells(Rows.Count, c).End(xlUp).Row
        For r = lr To 2 Step -1
                If InStr(Cells(r, c), ".") = 0 Then
                    Cells(r, c).Delete shift:=xlToLeft
                End If
        Next r
    Next c
End Sub

 

[diddi]

@MM
yeh it was getting a bit late when i was having a look. good exercise tho

 

[Michael M]

Yep, I know what you mean....I'm now waiting for someone to step up and reduce it to a couple of lines, as usual.....LOL !!

 

[Michael M]

Robert
I made a couple of changes to help with the housekeeping !!
It also now leaves the original text in column "A" untouched.
I must admit it's not elegant, but I think it will do the trick.

Sub Stripper() 'strips domain names from text string
Dim lr As Long, r As Long, LC As Integer, c As Integer
Application.ScreenUpdating = False
    Columns("A:A").Copy
    Columns("B:B").Insert Shift:=xlToRight
lr = Cells(Rows.Count, "B").End(xlUp).Row
        Range("B2:B" & lr).TextToColumns Destination:=Range("B2"), DataType:=xlDelimited, _
        ConsecutiveDelimiter:=True, Space:=True
    If WorksheetFunction.CountA(Cells) > 0 Then
        LC = Cells.Find(What:="*", After:=[A1], _
        SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    End If
    For c = LC To 2 Step -1
            lr = Cells(Rows.Count, c).End(xlUp).Row
        For r = lr To 2 Step -1
                If InStr(Cells(r, c), ".") = 0 Then
                    Cells(r, c).Delete Shift:=xlToLeft
                End If
        Next r
    For r = lr To 2 Step -1
                dot1 = Right(Cells(r, c), 1)
                dot2 = Left(Cells(r, c), 1)
                If InStr(".", dot1) = 1 Or InStr(".", dot2) = 1 Then
                    Cells(r, c).Delete Shift:=xlToLeft
                End If
        Next r
    Next c
Application.ScreenUpdating = True
End Sub