Excel Formula LEN and Subtitute

Question

So i have Image URLs that i need to split.

I tried this Formula

=RIGHT(N2,LEN(N2)-FIND("|",SUBSTITUTE(N2,"/","|",LEN(N2)-LEN(SUBSTITUTE(N2,"/","")))))

and it removed whole URL and just leaving the Image file name.

Example when i used the Formula: Image URL domain.com/images/products/72/252/19795/3/279/image-name.jpg

Result after using the formula image-name.jpg

The thing is i need to get the /279/ on the Result. So the result will looks like /279/image-name.jpg

What i need to change on the Formula that i used to also leave the /279/ text on the result ?

Answer 1

You could use:

Formula in B1:

=MID(A1,FIND("|",SUBSTITUTE(A1,"/","|",LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))-1)),LEN(A1))

Where we would first SUBSTITUTE() the second to last forward slash into a pipe. Then we FIND() it's postition and take the substring through MID().

A fun alternative is to split the string by its forward slash as a delimiter and join back the last two substrings:

=CONCAT(FILTERXML("<t><s>"&SUBSTITUTE(A1,"/","</s><s>/")&"</s></t>","//s[position()>last()-2]"))