3

I have a link that I pull in elsewhere. Let's say it looks like this: https://www.mysite.com/site/interesting-content/name-of-article.html?utm_source=source&utm_campaign=ab&utm_medium=medium&utm_term=paid

I've been able to truncate it down to /site/interesting-content/name-of-article.html?utm_source=source&utm_campaign=ab&utm_medium=medium&utm_term=paid using a substitute function.

But I'd like to further truncate it down to /site/interesting-content/name-of-article.html

The problem is, there's quite a few UTM campaigns that I can pull links from. So ideally, I'd like to remove anything after .html, without having to specify exactly what needs to be removed.

I tried using a right() function to get a copy of what I want to remove, so that in another cell I could do a substitute function on it to remove the unneeded bits:

=RIGHT(W8,SEARCH(".html",W8))

But it ended up looking like this: ent/name-of-article.html?utm_source=source&utm_campaign=ab&utm_medium=medium&utm_term=paid

1
  • If there's a better way of doing what I want it to do, without using a right function, I'm all ears, that's just currently what I'm trying. Jan 11 '18 at 13:54
3

Try replacing with REGEXREPLACE:

=REGEXREPLACE(W8,"\?[^?]*$","")

Explanation

REGEXREPLACE takes string (first parameter), then using pattern (second parameter) finds text that starts with ? until end of string, and replaces it with empty string (third parameter).

2

Assuming that W8 has the complete URL, use MID

 =MID(W8,SEARCH("/",W8,9),SEARCH(".html",W8)+5-SEARCH("/",W8,9))

SEARCH("/",W8,9) finds the start position SEARCH(".html",W8)+5-SEARCH("/",W8,9) finds the number of characters to get from the W8.

This formula is longer than using REGEXREPLACE but could be easier to understand for those who don't know about regular expressions besides it works on Google Sheets, Excel and other spreadsheets.

1
  • This is EXACTLY what I need. Perfect. Thank you!!! Jan 12 '18 at 14:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.