Excel Formula needed in separate column

问题:

I can't figure this out, Maybe you guys can help me.

I need to separate just the image name for example

xxqti8eli5h2f4abpiz2.jpg
lvfdpujvgkf75ve8ikob.jpg

In a separate column, I've a list of 8000 image name which I need to separate out any help from you guys is much appreciated.

http://images.XXX.com/image/upload/s--B3cI5sks--/c_limit,cs_srgb,h_600,w_600/xxqti8eli5h2f4abpiz2.jpg
http://images.XXX.com/image/upload/s--_3R1kbWq--/c_limit,cs_srgb,h_600,w_600/lvfdpujvgkf75ve8ikob.jpg

回答1:

Since the image is always the last chunk of your string when you separate/split the string by a forward slash, you can use a really tiny UDF to get this functionality.

In a new VBE Module (Alt+F11, then right click your workbook and Insert>>Module), paste the following:

Function get_image(url As String) As String
    'extract the last token from the url when it is split by forward slash
    get_image = Split(url, "/")(UBound(Split(url, "/")))
End Function

Now you can use this new function in your worksheet:

回答2:

formula version:

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