Forum Discussion
Solved
Excel Formula Problem
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:
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
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))," ","-")
- 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')")- =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"))
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, " ", "-"))
- 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.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))," ","-")
