VBA Find and Replace Part of a URL

[tomgrandy]

Here is my dilemma that might seem easy to you all, but am unsure how to write a VBA to accomplish.

I have a cell that I need to do a find and replace on up to the very last part (/1.jpg, /2.jpg, etc.)

The original cell info is as follows:

https://images.sitename.com/AuctionImages/3173/239780/FullSize

/1.jpg

https://images.sitename.com/AuctionImages/3173/239780/FullSize/2.jpg

https://images.sitename.com/AuctionImages/3173/239780/FullSize/3.jpg

What I need it to do when the VBA runs is rename it to (URL and path in blue to identify what needs replaced.

sites/default/files/images/auctions/AUCTION_DATE/1.jpg
sites/default/files/images/auctions/AUCTION_DATE/2.jpg
sites/default/files/images/auctions/AUCTION_DATE/3.jpg

Thanks in advance!

 

[tomgrandy]

Another option to try:

Sub tomgrandy_1()
Dim i As Long
Dim c As Range
Dim tx As String
Dim va, ary

va = Range("A1", Cells(Rows.Count, "A").End(xlUp))
tx = "sites/default/files/images/auctions/AUCTION_DATE/"
For i = 1 To UBound(va, 1)
    ary = Split(va(i, 1), "/")
    va(i, 1) = tx & ary(UBound(ary))
Next
Range("B1").Resize(UBound(va, 1), 1) = va
End Sub

Example:

Book1
	A	B
1	https://images.sitename.com/AuctionImages/3173/239780/FullSize/1.jpg	sites/default/files/images/auctions/AUCTION_DATE/1.jpg
2	https://images.sitename.com/AuctionImages/3173/239780/FullSize/2.jpg	sites/default/files/images/auctions/AUCTION_DATE/2.jpg
3	https://images.sitename.com/AuctionImages/1111/239780/FullSize/3.jpg	sites/default/files/images/auctions/AUCTION_DATE/3.jpg
4	https://images.sitename.com/AuctionImages/3333/222222/FullSize/4.jpg	sites/default/files/images/auctions/AUCTION_DATE/4.jpg
Sheet4

@Akuini - Thanks for the solution!! Works impressively well. Much appreciated.

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.