Forum Discussion

Copper Contributor

Feb 16, 2023

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 ...

Formulas and Functions

HansVogelaar
Feb 17, 2023
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))," ","-")

JeffG4710

Copper Contributor

Feb 16, 2023

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.

HansVogelaar

MVP

Feb 17, 2023

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))," ","-")

JeffG4710
Copper Contributor
Feb 17, 2023
HansVogelaar Hans, that formula worked perfectly. Thank you so much. I appreciate it.