Access Excel extract file name from file path

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.

excel_extract_file_name_01

 

Now press Ctrl+F to open the Find and Replace box
In Find what, type *\ 

excel_extract_file_name_02

 

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.

excel_extract_file_name_03
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

https://support.office.com/en-us/article/Find-or-replace-text-and-numbers-on-a-worksheet-0e304ca5-ecef-4808-b90f-fdb42f892e90

 

Wyman W
Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *