1

I'm attempting to figure out how to combine these two formulas when there are multiple inputs.

Example inputs:

https://tracking.com/123456789 https://tracking.com/123456789?ABCD

=SUBSTITUTE(
   SUBSTITUTE(XXX,"http://tracking.com/",""),
   "?ABCD","")
=REGEXREPLACE(XXX,"https://tracking.com/",)

Output should only show: 123456789

Does anyone know how to make this work?

2
  • 1
    Hi and welcome! May be =REGEXEXTRACT(<source cell>,"\d+")? See help on REGEXTRACT()
    – JohnSUN
    Commented Aug 9, 2022 at 6:12
  • It worked! Thank you!
    – Chris
    Commented Aug 9, 2022 at 7:09

2 Answers 2

1

Since your first formula works with both of the examples you provided, I believe you want to get the same results with your second formula as well. In this case, just change it as follows:

=REGEXREPLACE(REGEXREPLACE(<source cell>,"https://tracking.com/",),"\?ABCD",)
  • note that the question mark had to be escaped with a backslash because by itself it would have meant something else [?, if unescaped, matches the previous token between zero and one times, as many times as possible, giving back as needed (greedy)]
0

@JohnSUN's suggestion is the perfect if the tracking number contains only numbers and there are no numbers anywhere else in the URL:

=REGEXEXTRACT(A1,"\d+")

Based on your original approach, the following will grab the string at the end ignoring a trailing question mark with or without additional characters, and will work for HTTP and HTTPS:

REGEXEXTRACT(A1, "tracking.com\/(.*?)(?:\?.*)?$") 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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