Searching backwards through string.

[revver]

I have a column (D) of fully qualified file names eg

D:\folder\another folder\bigfolder\filename.bmp
D:\largefolder\morefolder\folder\another folder\nextfolder\smallfile.jpg

I want to extract just the filename to column G.
To do this I expect I will need to search backwards through the filename string looking for the final "\"
From that I can just slice off the filename I want.

Is there a nest of intrinsic functions I can use or is it easier to create a user defined function?

 

[Rick Rothstein]

Give this formula a try...

=TRIM(RIGHT(SUBSTITUTE(D1,"\",REPT(" ",260)),260))

 

[revver]

Thanks Rick. It works a treat.
Now I have to figure how it works.
I know TRIM and RIGHT but I'll have to hit the books for REPT and SUBSTITUTE
Thanks again.

 

[Rick Rothstein]

Now I have to figure how it works.

The formula replaces each backslash with 260 spaces (where 260 is the maximum length of a path in Windows). What that means is the filename (the part you want) is separated from the rest of the text by 260 spaces. Now we take the right 260 characters from the end... in there is the complete filename plus some leading blank spaces, so we apply the TRIM function to get rid of them.

 

[revver]

Yep. Figured it out but not the reason for using 260. Suspected it had to be to do with path length but I thought that was 255. Now I know. Thanks.