VBA: Find / Replace then add to end of string

[Dubyah]

Hey guys, I have a large file of links formatted like 'http://www.website.com/page/page/page.html'

Looking for some VBA to clean it up First I need the 'http://www.website.com' removed and replaced with '^' second I need a string added to the end of the link.

Here is what I have for adding text to the end of the string.. Having trouble with the remove and replace.

Sub add()    Dim c As Range
    For Each c In Selection
        If Not IsNumeric(c) Then
            c.Value = c.Value & "STRING"
        End If
    Next c
End Sub

Feel free to direct me to a page that may help me out.. Looked around and couldn't find what I needed. But maybe I'm searching for the wrong thing..

 

[Scott Huish]

Why are you checking for Numeric?
If your values are always in a set place, you can avoid Select as well.

This should work for Column A:

Sub test()
Dim c As Range
For Each c In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
    If Left(c, 5) = "http:" Then
        c = Mid(c, InStr(9, c, "/"))
        c = "^" & c & "STRING"
    End If
Next
End Sub

 

[Max1616]

I would use the worksheet function substitute:

Dim RemoveS, AddS, MyString As String
Dim c As Range

RemoveS = "http://www.website.com/page/page/page.html"
AddS = "^"
MyString = "String"


For Each c In Selection
    If Not IsNumeric(c) Then
        c.Value = WorksheetFunction.Substitute(c.Value, RemoveS, AddS) & MyString
    End If
Next c