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))," ","-")
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))," ","-")
HansVogelaar Hans, that formula worked perfectly. Thank you so much. I appreciate it.
