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

 

[Rick Rothstein]

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

Does this formula do what you want...

=IF(ISNUMBER(SEARCH("myurl.com",A1)),LEFT(A1,FIND(CHAR(1),SUBSTITUTE(A1,"/",CHAR(1),5)))&"refer/?size=web","")

 

[mvrht]

Hi Rick,

No joy with that I'm afraid. it just cuts a lot of the URL out, see rows 10 and 11 here
https://docs.google.com/spreadsheets/d/19hrdqoYbcS8qT9LDr-AH_C7GeflzAL_4OAo_1HHYghg/edit?usp=sharing


the best solution so far is
=if(regexmatch(B9, "myurl.com"), LEFT(B9,SEARCH("/",B9,29))&"refer/?size=web", B9)

although if they enter without the http or www it could be an issue. So something similar that focuses on that 5th slash would be ideal

 

[jolivanes]

Confused.
In post #1 you ask to delete everything after the 3rd forward slash.
However, your example in post #1 shows after 5 slashes. I guess you don't count the double slash at the beginning.
What is the result of Post #6 ?

 

[Rick Rothstein]

Hi Rick,

No joy with that I'm afraid. it just cuts a lot of the URL out, see rows 10 and 11 here
https://docs.google.com/spreadsheets/d/19hrdqoYbcS8qT9LDr-AH_C7GeflzAL_4OAo_1HHYghg/edit?usp=sharing

It seems to work correctly for me.