Extract File Names from Path

Extract File Names from path :-  

Suppose we have a file path " C:\documents\personal\myfiles.xls ", and we have to extract only file name from it. i.e "myfiles.xls".

See below print shot for a reference :

Use formula :- =RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1,"\","*",LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))))

Explanation :
LEN i.e length function used first to count the total length of path i.e LEN(A1) and then we subtract the total length of path without slash "\" (and without slash would be obtained by substituting it with blank with the help of SUBSTITUTE function.) i.e LEN(SUBSTITUTE(A1,"\","")) . By doing this we get to know total number of slash "\" in a path i.e LEN(A1)-LEN(SUBSTITUTE(A1,"\","")).

Once we get total number of slash  "\" in it, we distinguish the last slash with star "*", as only after the last slash "\" our required data exist i.e file name and for this SUBSTITUTE function is helpful i.e SUBSTITUTE(A1,"\","*",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))) . Note, LEN(A1)-LEN(SUBSTITUTE(A1,"\","")) as we found it before placed it in [instance_num] of substitute function, works as pointing the last slash "\".

Then need to find the position of our placed star "*" in a path, and for it FIND function will be helpful. Since we are interested to find the data after the star "*" for that subtract the find function result with the total length, it gives the total length of the file name i.e LEN(A1)-FIND("*",SUBSTITUTE(A1,"\","*",LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))) .

And use RIGHT function for the words from the right equal to the total length of file name that are found.
i.e =RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1,"\","*",LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))))

Thank you!