Getting a file name from a file path in Excel

Excel

Oftentimes in my work I find myself needing to check if a particular file is in some directory or it's subdirectories. Usually you can do a simple Windows Explorer search, or even use a more powerful tool like WizFile to perform faster searches. But what if you have a big list of file names and need to see if they exist in a directory? In this case we can use Excel and a VLOOKUP to help us - but first you need to get all the file names from the paths...

= RIGHT(A1, LEN(A1) - FIND("*", SUBSTITUTE(A1, "\", "*", LEN(A1) - LEN(SUBSTITUTE(A1, "\", "")))))

Example

If you need to get a list of all file paths in a directory and sub-directories, open command prompt and navigate to the folder. Then run dir /s /b > FileList.txt, and you will get a new file, "FileList.txt" in that folder with the paths of every file and folder!

Explanation

The approach here is to replace the last slash with an asterisk, which will mark where the file name starts. Then we can just take everything after that asterisk as the file name. On Windows, an asterisk cannot be in a filepath so it is a good character to use to 'mark' where the filename starts.

SUBSTITUTE has an optional parameter instance_num, which allows you to determine which instance of the "old text" you want to replace. For example, if I did SUBSTITUTE("food", "o", "*", 2), it would replace only the second "o" with an asterisk "*", and the result would be fo*d. So, the way we get the number of slashes is by subtracting the length of the text with slashes removed from the length of the original text. Again, with the example above, if I remove the o's from "food": LEN("food") - LEN("fd") = 2, so there are 2 o's in "food".

Number of Slashes = LEN(A1) - LEN(SUBSTITUTE(A1,"\","")
Text with last slash replaced = SUBSTITUTE(A1,"\","*", LEN(A1) - LEN(SUBSTITUTE(A1,"\",""))

Finally, we take the text after the asterisk as the file name.

= RIGHT(B2, LEN(B2) - FIND("*", <Text with last slash replaced>))

This can also be done with MID:

= MID(A1, FIND("*", <Text with last slash replaced>) + 1, LEN(B2))

Putting all that together gives you the full formula:

= RIGHT(A1, LEN(A1) - FIND("*", SUBSTITUTE(A1, "\", "*", LEN(A1) - LEN(SUBSTITUTE(A1, "\", "")))))

Note: If the cell only contains a file name with no slashes (e.g. "MyFile.txt"), the result will be a #VALUE error because no slash "\" can be found. This can be resolved by wrapping the whole formula in an IFERROR function.

© 2021