Excel Formula Problem

New Contributor

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:


I want to change it into the following:


I am using a formula that looks like the following:



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?




6 Replies



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


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

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.
try regular expressions\d+$@~


=webservice("" & encodeurl("\d+$"))
best response confirmed by JeffG4710 (New Contributor)


In older versions of Excel:


=SUBSTITUTE(TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D2,"",""),"-gm","-royalty-free-image/"),"-",REPT(" ",255)),LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D2,"",""),"-gm","-royalty-free-image/"),"-",REPT(" ",255)))-255))," ","-")

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