Remove anything after the 3rd / and append some data

[mvrht]

We have various urls posted into our sheet that sometimes are not valid urls for what we need. So we need to strip some data from it and append some data to the end.

EXAMPLE

https://www.myurl.com/p/Bce6o9tg0XZ/?utm_source=google&utm_medium=cpc

needs stripping to

https://www.myurl.com/p/Bce6o9tg0XZ/

and then appending with
refer/?size=web

Leaving me with

https://www.myurl.com/p/Bce6o9tg0XZ/refer/?size=web

Ideally this needs doing on the fly, so when an invalid url is entered. The function converts it and adds the part on the end, so perhaps I can apply a function to the column or within Google App Script using Javascript

I have had a tinker with the functions here which only deal with the extraction part but no joy yet
https://stackoverflow.com/questions/20717566/excel-remove-everything-after-in-a-url

 

[hiker95]

mvrht,

Welcome to the MrExcel Forum.

My PC was just recently upgraded to the latest version of Windows 10.

You first three links are returning: This site is not secure

 

[Rick Rothstein]

mvrht,

Welcome to the MrExcel Forum.

My PC was just recently upgraded to the latest version of Windows 10.

You first three links are returning: This site is not secure

Windows 8.1 is balking also. It says the "https" security designation is not correct and that the site is actually not secure at all. Given that, I think it would be prudent for the OP to do a virus scan on his computer since he obviously visited the underlying website.

 

[igold]

I am surprised that hiker even clicked on the links. Given that this is your first post and your question is pretty dependent on clicking the links, there is no way I am clicking.

You may want to find a way to describe what your requirements without the links.

 

[igold]

Windows 8.1 is balking also. It says the "https" security designation is not correct and that the site is actually not secure at all. Given that, I think it would be prudent for the OP to do a virus scan on his computer since he obviously visited the underlying website.

Or he would like others to click on the links, if you catch my drift.

 

[jolivanes]

In the meantime, if he/she wants to continue visiting sites that are not secure, this might do it (not automatically though).

Sub Maybe()
Dim c As Range, a
For Each c In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
	a = Split(c, "/")
	c.Offset(, 2).Value = Left(c, Len(c) - Len(a(UBound(a)))) & "refer/?size=web"
Next c
End Sub

 

[Fluff]

I was thinking that the links weren't links per-se, but simply examples of what the OP was trying to achieve.

 

[Rick Rothstein]

I was thinking that the links weren't links per-se, but simply examples of what the OP was trying to achieve.

Assuming you are correct, then I think this formula will produce the results the OP wants...

=LEFT(A1,FIND("",SUBSTITUTE(A1,"/","",5)))&"refer/?size=web"

 

[István Hirsch]

@ mvrht

Actually, where do you want to cut the string, after the 3rd or the 5th "/"?

 

[mvrht]

I have put it in a Google Sheet for clarity. I have managed to get a temp solution for url extraction but it needs more work/help
https://docs.google.com/spreadsheets/d/19hrdqoYbcS8qT9LDr-AH_C7GeflzAL_4OAo_1HHYghg/edit?usp=sharing