This Excel tutorial explains how to extract file name from file path using VBA and without VBA.
Excel Access VBA extract percentage from text
Access Excel VBA extract number from text or extract alphabet
Excel Extract Time from Date Time or Extract Date
Excel extract file name from file path
Assume that you have a file path such as
C:\Program Files\Google\GoogleToolbarNotifier\a.jpg
If you want to extract file name a.jpg from the path in Excel, first we need to think about how to define file name in the path. We can assume that the text after last backslash \ is the file name, while the text before the last backslash is file path.
Excel extract file name from file path (non VBA)
Given that you have the list of file path in column B.
Now press Ctrl+F to open the Find and Replace box
In Find what, type *\
The asterisk is known as wildcard, you can use other wildcard as well
| Wildcard | Meaning | Example |
| * | Represents one or more characters (any character) | J* any text that starts with J *J starts with any text but ends with J *J* any text that has J in the middle |
| ? | Represents one character (any character) | J? 2 characters that start with J ?J 2 characters that end with J ?J? 3 characters with J in the middle |
| ~ | Treat * or ? as the actual character but not wildcard. Used ~ followed by * or ? | J~** any text that starts with J* ~**J any text that starts with * and ends with J ~?*~* any text that starts with ? and ends with * |
Now press Replace All, the text before the last slash \ are removed, only the file name is left in column B.
If you don’t want to replace all file path in the worksheet, you can also select specific Cells and then press the Replace All button.
Access Excel extract file name from file path (VBA)
VBA Code
Below is a custom Function to get the file name from file path. Split Function can delimit the path by backslash , and then store the result in array. We use UBound to return the last item in the array.
Public Function extractFileName(filePath As String) As String extractFileName = Split(filePath, "\")(UBound(Split(filePath, "\"))) End Function
Example
| Path Name | Formula | Result |
| C:\Users\WYMAN\Pictures\discuss.jpg | =extractFileName(A2) | discuss.jpg |
| C:\Users\WYMAN\Desktop\cognitive psy\example.pdf | =extractFileName(A3) | example.pdf |
| C:\test | =extractFileName(A4) | test |
Get the file path without file name (VBA)
You can use Workbook.Path Method to get the path without file name.
For example, if the full path is C:\Users\WYMAN\Pictures\discuss.jpg
Run the below code to get C:\Users\WYMAN\Pictures (without back slash)
Activeworkbook.Path
Outbound References