Excel Formula Problem

[JeffG4710]

I have a variable string of characters that I want to change into another string.  The characters and delimiters are somewhat variable but there is a pattern.

I have a string that looks like the following:

https://www.istockphoto.com/photo/upper-colorado-river-gm1466493937-498485667

I want to change it into the following:

https://www.gettyimages.com/detail/photo/upper-colorado-river-royalty-free-image/1466493937

I am using a formula that looks like the following:

=SUBSTITUTE(SUBSTITUTE(D2,"istockphoto.com/photo","gettyimages.com/detail/photo"),"-gm","-royalty-free-image/")

The result using this formula looks like the following:

https://www.gettyimages.com/detail/photo/upper-colorado-river-royalty-free-image/1466493937-49848566...

I would like to delete that last string of numbers following the last "-".  Is there any way in my Excel formula that I can do that?

Thanks

[#2]

@JeffG4710 

If you have Microsoft 365 or Office 2021 (or use Excel Online):

=LET(q, D2, r, SUBSTITUTE(q, "istockphoto.com/photo", "gettyimages.com/detail/photo"), s, SUBSTITUTE(r, "-gm", "-royalty-free-image/"), t, SUBSTITUTE(s, "-", REPT(" ", 255)), u, LEFT(t, LEN(t)-255), v, TRIM(u), SUBSTITUTE(v, " ", "-"))

[#3]

Hans,
Thanks for responding. I plugged your formula in and didn't get any result. I forgot to say what version of Excel I use. I have Microsoft Office Home and Business 2016. Also, I wanted to say that the number of "-" in the character string is variable.

[#4]

try regular expressions
@istockphoto.com/photo@#@-gm@#@-\d+$@~

or

=webservice("http://e.anyoupin.cn/eh3/?regreplace~encodeurl('@istockphoto.com/photo@#@-gm@#@-\d+$@~gettyimages.com/detail/photo#-royalty-free-image/#~https://www.istockphoto.com/photo/upper-colorado-river-gm1466493937-498485667')")

[#5]

=webservice("http://e.anyoupin.cn/eh3/?regreplace~" & encodeurl("@istockphoto.com/photo@#@-gm@#@-\d+$@~gettyimages.com/detail/photo#-royalty-free-image/#~https://www.istockphoto.com/photo/upper-colorado-river-gm1466493937-498485667"))

[#6 · Accepted solution]

@JeffG4710 

In older versions of Excel:

=SUBSTITUTE(TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D2,"istockphoto.com/photo","gettyimages.com/detail/photo"),"-gm","-royalty-free-image/"),"-",REPT(" ",255)),LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D2,"istockphoto.com/photo","gettyimages.com/detail/photo"),"-gm","-royalty-free-image/"),"-",REPT(" ",255)))-255))," ","-")

[#7]

@Hans Vogelaar Hans, that formula worked perfectly.  Thank you so much.  I appreciate it.