Extracting File Name from string

[AlanAnderson]

Hi, me again ---

I need to extract a name out of a long, variable string. The problem is that I do not know what folders and subfolders will be in string nor do i know the exact length of the file name. If I did, then a simple right() would do it.
Examples of just three of the strings are:

C:\Wf\SavedQuotes\CustQuoteKKK777.xls
C:\Wf\SavedQuotes\2011-03-02 15-25-08\CustQuote123abcdef.xls
C:\Wf\2011-03-03 18-15-087\CustQuoteabcde66554.xls
(There is no way I can predict or restrict the folders or number of sub folders involved)

The only data I can depend on is that my filename will always start with CustQuote (unknown number of characters ) and end with .xls

In first example the answer i need to obtain is:

CustQuoteKKK777

The logic behind this is too much for me. Can someone please help?

Thanks,

Alan
Excel 2003 but must also work on later versions.

 

[Andrew Poulsom]

See the FileNameOnly function here:

http://spreadsheetpage.com/index.php/tip/some_useful_vba_functions/

 

[AlanAnderson]

Hi Andrew,
That works like a dream - not sure I fully understand why but it is great.
Thank you so much.

Regards,

Alan