Sharid
Well-known Member
- Joined
- Apr 22, 2007
- Messages
- 1,064
- Office Version
- 2016
- Platform
- Windows
I have a list of urls in Sheet2 column C, starting from row 2 down. I need to extract parts of the url and change it. In the image below I need the part in Blue.
From
I need the NEW urls to replace the urls in Column C and NOT to put the results into A NEW Column as my code below does. It also stripping the wrong part of the url, as it Stripped the word YellowPages and NOT the bit shown above in blue
Sorry but had to post urls as images as they kept turning in to links. I have been stuck on this for a few days now and can not work it out as I am not good a I would prefer it with vba or a formula. Vba if possible as it works faster
From
I need the NEW urls to replace the urls in Column C and NOT to put the results into A NEW Column as my code below does. It also stripping the wrong part of the url, as it Stripped the word YellowPages and NOT the bit shown above in blue
VBA Code:
Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws As Worksheet
Set ws = wb.Sheets("URLs")
''' STRIP A DOMAIN
ws.Range("D2:D" & ws.Range("C" & Rows.Count).End(xlUp).Row).Formula = "=TRIM(MID(SUBSTITUTE(C2,""."",REPT("" "",999)),999,999))"
''' STRING Url Maker
ws.Range("E2:E" & ws.Range("D" & Rows.Count).End(xlUp).Row).Formula = "=(""https://www.""&SUBSTITUTE(D2, "" "", """")&""/"")"
Sorry but had to post urls as images as they kept turning in to links. I have been stuck on this for a few days now and can not work it out as I am not good a I would prefer it with vba or a formula. Vba if possible as it works faster