How to extract filename from path\filename

**mpan** · 05-25-2012, 09:10 AM

I have several thousand path/filenames in a spreadsheet, one path\filename per cell in column A. I'm trying to figure out a formula that will extract the filename only. The problem is finding the last "\" and then extracting everything to the right. There could be zero or many "\" characters. Any help is appreciated. Sample data:

c:\FileB.jpg
controls.txt
c:\documents and settings\UserA\FileA.txt
c:\SOP\containers\dishrack\types\explanation.doc

Thanks, Mark

**NBVC** · 05-25-2012, 09:13 AM

Assuming data starts in A1, try:

=TRIM(RIGHT(SUBSTITUTE(A1,"\",REPT(" ",100)),100))

copied down

**vandan_tanna** · 05-25-2012, 09:33 AM

Ingenious, NBVC!

**mpan** · 05-26-2012, 01:17 AM

Wow, I am humbled, NVBC. This works perfectly. I spent many hours experimenting and searching the net before I posted here. I will study it, understand it, and add new tools to my Excel toolbox. Thank you very much!

Mark