Extract file name from path in Excel
To do it in Excel, here is the answer:
a) Enter the formula =TRIM(RIGHT(SUBSTITUTE(A2,"\",REPT(" ",100)),100)) as shown below where A2 corresponds to the cell containing the full path with file name.
Basically this formula replaces all "\" characters with 100 blank spaces. Then the right most 100 characters is extracted and trimmed to get the file name. 100 spaces is used assuming file name length will be less than 100 characters.
Technically the file name could be 255 characters long in which case 100 in the formula should be replaced by 255.
You can find similar Excel Questions and Answer hereunder
1) How can I set up a dynamic named range that expands automatically when new items are added to the list?
2) How can I convert Column numbers into Column names for use in range definition?
3) Here an explanation about the file dialog and how to control in with VBA
4) How can I get users to select a folder to save the output of my macro?
5) How can I extract file name from a full path including folder path and file name?
6) Can I assign a NAME to a constant (similar to declaring constants in coding environment like VBA)?
7) After several iterations, I have finalized my WorkBook - how do I make it Read-only from then on to prevent further edits?
8) Write to text file without quotes in vba in Excel
9) How to use the name manager in Excel to have a good overview of the various variables and their names in Excel
10) How can I extract First Name and Last Name from a cell that has Full name?