Excel VBA extract url form src or href

Question

I have been working with this code for a while, it leaves only the url in the selected area, removing what is unnecessary, it works correctly but only for one url, if more than one url appears in one cell it will leave only the first one, additionally if the url is linked to another it will interpret it as one url, and the idea is to separate them. I tried modifying the code or using ideas from other scripts, but it didn't work for me. The main thing is to leave only the url in the selected area, each on a new line.

Public Function ExtractURL(ByVal InputVal As String) As String
    
    Dim StartPos As Long
    StartPos = InStr(1, InputVal, "http")
    
    If StartPos > 0 Then
        Dim EndPos As Long
        EndPos = InStr(StartPos + 1, InputVal, """")
        
    
        If EndPos > 0 Then
            ExtractURL = Mid$(InputVal, StartPos, EndPos - StartPos)
        End If
    End If
    
End Function

Sub url_change_cell_select()
    Dim RetVal As String
Dim cell As Range
For Each cell In Selection.Cells
    RetVal = ExtractURL(cell.Value)
    If RetVal <> vbNullString Then
        cell.Value = RetVal
    End If
Next cell
    Load KOD
    KOD.Show
End Sub

VBasic2008 · Accepted Answer · 2022-12-15 21:35:55Z

1

Extract URLs

The Method

Sub ReplaceWithExtractedURLs()
    
    If Selection Is Nothing Then Exit Sub
    If Not TypeOf Selection Is Range Then Exit Sub
    
    Dim cell As Range
    Dim rURL As String
    
    For Each cell In Selection.Cells
        rURL = ExtractURL(CStr(cell.Value))
        If Len(rURL) > 0 Then cell.Value = rURL
    Next cell
    
    'Load KOD
    'KOD.Show

End Sub

The Function

Function ExtractURL(ByVal InputString As String) As String
    
    Const lYes As String = "http"
    Const rNo As String = """"
    Const Delimiter As String = vbLf
    
    Dim tSubs() As String: tSubs = Split(InputString, lYes)
    
    Dim rString As String, tSub As String, tStr As String
    Dim n As Long, ePos As Long
    
    For n = 1 To UBound(tSubs)
        tSub = tSubs(n)
        ePos = InStr(1, tSub, rNo)
        If ePos = 0 Then tStr = tSub Else tStr = Mid(tSub, 1, ePos - 1)
        rString = rString & lYes & tStr & Delimiter
    Next n
    
    If Len(rString) > 0 Then
        rString = Replace(Left(rString, Len(rString) - Len(Delimiter)), _
            Delimiter & Delimiter, Delimiter)
        ExtractURL = rString
    End If
    
End Function

edited Dec 15, 2022 at 21:35

answered Dec 15, 2022 at 21:21

VBasic2008

56.9k5 gold badges21 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Korliki Over a year ago

Thank you for the response, i was trying to use it but got error :( i.ibb.co/Jpdxz7t/16-12.png Is it possible to simplify the code by disabling the function of separating the url if they are connected? so that there is only the option to pull the multiple urls if they are separated as in the photo above in column B and C, and in column A to remain one url as the combined one?

VBasic2008 Over a year ago

You have possibly used Split to name one of your procedures. Give it another name and this solution will work.

Korliki Over a year ago

ahh yes i had other module named split, it's working now, thank you very much. I still have a question for the future, if I would like to disable the function of splitting the merged url to a new line, what do I need to change? the point is that when cell has connected url like this, example1.com/https://expample2.com to leave it merged, dont split it to new line.

Korliki Over a year ago

I managed to modify the code so that it no longer separates the linked url, in addition i added to separate the url with a new, line, thanks again! I changed these lines: Const lYes As String = """http" and and added new line and change one nYes = Right(lYes, Len(lYes) - 1) rString = rString & nYes & tStr & Delimiter & vbNewLine

Collectives™ on Stack Overflow

Excel VBA extract url form src or href

1 Answer 1

Extract URLs

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Extract URLs

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related