CODEWITHSUNDEEP
regexexcelhyperlink
j0nr
j0nr

Reputation: 301

Search and Replace Certain Extensions in Hyperlinks in Excel

I have an excel file with a load of hyperlinks. Some of the hyperlinks match a certain pattern, lets say file:///\\location\file_name_<xxx>.pdf, where <xxx> varies. There are also other hyperlinks that are similar, say file:///\\location\other_file_<xxx>.pdf.

I want to be able to search and replace all hyperlinks matching (using simple * wildcard for explanation) *file_name*.pdf and replace with *file_name*.mht.

So any hyperlink to a PDF matching file_name_<xxx> replace its extension with mht.

Upvotes: 0

Views: 206

Answers (1)

shockawave123
shockawave123

Reputation: 697

You can use regular expressions by using Visual Basic under the developer tab. You have to do quite a bit of coding. I would offer my own explanantion, but the answer to this question has been explained pretty thoroughly in another stackoverflow question. Click here >> (How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops)

In Summary:

Step 1: Add VBA reference to "Microsoft VBScript Regular Expressions 5.5"

Step 2: Define your pattern

Step 3: Run as macro, Run as an in-cell function, or Loop Through Range

I believe the example that would be the most help for you would be Example 4: 

Private Sub splitUpRegexPattern()
Dim regEx As New RegExp
Dim strPattern As String
Dim strInput As String
Dim strReplace As String
Dim Myrange As Range

Set Myrange = ActiveSheet.Range("A1:A3")

For Each C In Myrange
    strPattern = "(^[0-9]{3})([a-zA-Z])([0-9]{4})"

    If strPattern <> "" Then
        strInput = C.Value
        strReplace = "$1"

        With regEx
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
            .Pattern = strPattern
        End With

        If regEx.test(strInput) Then
            C.Offset(0, 1) = regEx.Replace(strInput, "$1")
            C.Offset(0, 2) = regEx.Replace(strInput, "$2")
            C.Offset(0, 3) = regEx.Replace(strInput, "$3")
        Else
            C.Offset(0, 1) = "(Not matched)"
        End If
    End If
Next
End Sub

Upvotes: 2

Related Questions