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 ...
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')")
@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"))