Removing the tail end from URLs

[John_Krueger1]

Hey AT Community!

Is there a way to remove the tail ends from formulas in bulk?

Example-

I have 3 urls in 3 different rows:

https://google.com/index?test
https://yahoo.com/example?test
https://apple.com/ending?test

Using a formula (or something else) I’d like to get rid of the tails so that I am left with:

https://google.com/
https://yahoo.com/
https://apple.com/

I feel like this should be possible?

Appreciate any pointers!

[TheTimeSavingCo]

Hey John, try:

REGEX_REPLACE(
  {Text},
  '\\?(.*)',
  ''
)

[Justin_Barrett]

@Adam_TheTimeSavingCo Your example removes everything starting with the question mark, but John was asking about taking away everything after the last forward slash. Here’s what I came up with:

IF({URL}, REGEX_REPLACE({URL}, "[^/]*$", ""))

This focuses on the end of the string and strips away everything that’s not a forward slash.

[John_Krueger1]

Justin_Barrett:

IF({URL}, REGEX_REPLACE({URL}, "[^/]*$", ""))

@Justin_Barrett Thanks so much! This is definitely heading in the right direction. This works really great for most of my links.

I should have been more clear, because I have a bunch of different link formats with different amounts of /'s.

For example:

https://google.com/index/test
https://yahoo.com/example/test/e231/

It seems like really, the common denominator is that I want to keep everything to the left of the 3rd /.

Is there a way to modify the formula to approach it from that side?

[Bill_French]

John_Krueger1:

I want to keep everything to the left of the 3rd /

I was thinking -

REGEX_REPLACE({URL}, "^https?:\/\/[^\/]+", "")

[kuovonne]

John_Krueger1:

I should have been more clear, because I have a bunch of different link formats with different amounts of /'s.

Having a complete set of test data that covers all use cases is always important. When using REGEX, a complete set of test data is absolutely essential!

[John_Krueger1]

@Bill.French Interesting, thanks for sharing!

This formula is giving me everything after the 3rd “/”. What I’m looking for is essentially the opposite, everything before (can include) the 3rd “/”.

[kuovonne · Accepted solution]

Bill.French:

I was thinking -

REGEX_REPLACE({URL}, "^https?:\/\/[^\/]+", "")

What about

IF(
    REGEX_MATCH({URL}, "https?:\/\/[^\/]+"),
    REGEX_EXTRACT({URL}, "https?:\/\/[^\/]+")
)

[John_Krueger1]

@kuovonne appreciate the feedback! The data is confidential at this point so I’m unable to share it in bulk.

[John_Krueger1]

You nailed it! Thanks so much @kuovonne. This is exactly what I needed :slightly_smiling_face: