I am having this type of urls in multiple excel cells:
https://stock-media-files.co/previews/e-C-10071-z-a/filename.jpg
Of course the address is always different so REPLACE cannot be used
I want to keep only filename.jpg in every cell
Assuming that my url in at cell A1 tried with this formula:
=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND("|",SUBSTITUTE(A1,".","|",LEN(A1)-LEN(SUBSTITUTE(A1,".",""))))-1),"\",REPT(" ",255)),255))
But throws error. Not working.
I am using Excel LTSC PRO 2021
Any help please?
>Solution :
Office 365: =TEXTAFTER(A1,"/",-1)
Where -1 means first found from the right of the text value.
Older: =TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",255)),255))
This changes all / into a space character repeated 255 times. Next it returns the last 255 characters of the amended string. This is the characters after the last found / and 255 minus the length of the string after the last /. TRIM removes al leading space characters, which leaves the string only.
Since your local settings have a , for decimal separator, your Excel can’t use , as a delimiter for it’s arguments. Therefor it uses ; (and likely \ for vertical array separator: ={1\2\3})