Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Oct 06, 2022 07:13 PM
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:
I feel like this should be possible?
Appreciate any pointers!
Solved! Go to Solution.
Oct 07, 2022 12:08 PM
What about
IF(
REGEX_MATCH({URL}, "https?:\/\/[^\/]+"),
REGEX_EXTRACT({URL}, "https?:\/\/[^\/]+")
)
Oct 06, 2022 07:22 PM
Oct 06, 2022 10:07 PM
@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.
Oct 07, 2022 09:35 AM
@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?
Oct 07, 2022 10:56 AM
I was thinking -
REGEX_REPLACE({URL}, "^https?:\/\/[^\/]+", "")
Oct 07, 2022 12:05 PM
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!
Oct 07, 2022 12:06 PM
@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 “/”.
Oct 07, 2022 12:08 PM
What about
IF(
REGEX_MATCH({URL}, "https?:\/\/[^\/]+"),
REGEX_EXTRACT({URL}, "https?:\/\/[^\/]+")
)
Oct 07, 2022 12:08 PM
@kuovonne appreciate the feedback! The data is confidential at this point so I’m unable to share it in bulk.
Oct 07, 2022 12:10 PM
You nailed it! Thanks so much @kuovonne. This is exactly what I needed :slightly_smiling_face: