extract file name from path in Excel

Q31. In Excel, how can I extract file name from a full path?

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.

excel extract file name from path

 

You can find similar Excel Questions and Answer hereunder

1) Write to text file without quotes in vba in Excel

2) How can I prevent users from seeing / accessing my macro code?

3) How can I change the meta-data of file (Author, Company Name and the like)?

4) How can I extract First Name and Last Name from a cell that has Full name?

5) How can I identify the cells that are dependent on a particular cell?

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) How can I set up a dynamic named range that expands automatically when new items are added to the list?

9) How can I loop through all WorkSheets and get their names using VBA?

10) How can I extract file name from a full path?

11) How can I get the last non-zero value in a row?

12) How can I extract file name from a full path including folder path and file name?

13) How can I get users to select a folder to save the output of my macro?

14) How can I find number of months that has elapsed given 2 dates?

15) How can I prevent users from entering duplicate values in a range?