Formula to extract substring between two characters

[taywl]

I have a list of path names in column A and would like to extract the file names which come after the last "/" and before the last "." to be placed in column B. I also want all "%20" to be replaced with a space.

For example, cell A1 contains:
Level%202/SB/C10%20illus/SIF%20BC%20SB2B%20C10%20(I)%20aw%20code.docx?d=wae252c17551145c7b4b3b69c4d44f532&csf=1&e=hb3jX4

Cell B1 should have:
SIF BC SB2B C10 (I) aw code

Is there a formula I can put in column B?

Thanks in advance!

 

[mikerickson]

Re: Need help with formula to extract substring between two characters

This is easiest to do in steps.
In B1, put the formula =TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",255)),255)) which gets the "after the last /"
in C1, =TRIM(LEFT(SUBSTITUTE(B1,".",REPT(" ",255)),255)) will get the "before the ." bit
and =SUBSTITUTE(C1,"%20", " ") will change the substring to spaces.

One could use a monster formula combining those, but it would be hard to edit.

 

[taywl]

Re: Need help with formula to extract substring between two characters

Wow didn't expect a reply this fast! thanks for that!