Remove anything after the 3rd / and append some data

mvrht

New Member
Joined
Dec 9, 2017
Messages
18
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
Code:
https://www.myurl.com/p/Bce6o9tg0XZ/?utm_source=google&utm_medium=cpc
needs stripping to
Code:
https://www.myurl.com/p/Bce6o9tg0XZ/
and then appending with
refer/?size=web

Leaving me with
Code:
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
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,691
Office Version
  1. 2010
Platform
  1. Windows
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.
 
Last edited:

igold

Well-known Member
Joined
Jul 8, 2014
Messages
2,506
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Jul 8, 2014
Messages
2,506
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Sep 5, 2004
Messages
1,690
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
In the meantime, if he/she wants to continue visiting sites that are not secure, this might do it (not automatically though).
Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,071
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,691
Office Version
  1. 2010
Platform
  1. Windows
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"
 

Watch MrExcel Video

Forum statistics

Threads
1,122,858
Messages
5,598,487
Members
414,243
Latest member
Shockpulsar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top