vba extract file name from full path in Excel
Q152. In Excel, how can I extract file name from a full path including folder path and file name?
For example, I have the full path to a file (C:\Users\Guest\Documents\Exercises\Intermediate.xlsx) - I want to extract just the file name, "Intermediate.xlsx".
To do it in Excel, here is the answer:
- Option Explicit
- Sub ReverseSearch()
- Dim sFolderPathwithFileName As String, sFileName As String
- Dim lngFileNamePosition As Long
- sFolderPathwithFileName = "C:\Users\Guest\Documents\Exercises\Intermediate.xlsx"
- lngFileNamePosition = InStrRev(sFolderPathwithFileName, "\")
- sFileName = Right(sFolderPathwithFileName, Len(sFolderPathwithFileName) - lngFileNamePosition)
- End Sub
a) This is a classic example of searching a string in reverse direction. Excel VBA has an inbuilt function "InStrRev" to determine the position of a "string" when searched from right to left.
b) Row 7 is used to determine the position of the last occurrence (rightmost) of "\". Any text to the right of the rightmost "\" is file name.
c) In row 8, text to the right of the rightmost "\" is extracted.