The best thing about Excel is that you can do the same thing in several ways. Our yesterdays problem – Extracting file name from full path is no different. There are many different ways to do it, apart from writing a formula. Learn these techniques to be a data extraction ninja.
1. Using Find Replace
Suggested by Iain in the comments yesterday, I love this technique for its simplicity and awesomeness.- Select all the file paths
- Press CTRL+H
- Type *\ in find field
- Leave the replace field empty.
- Click on Replace all.
- Done!
Thanks Iain for teaching us this trick.
2. Using Text to columns utility
Buried inside heap of features in Excel is this beautiful Text to columns utility, that can take any text and convert it in to many columns based on the delimiter you specify. [more uses of text to columns]This is how we can use it:
- Select all the file path cells
- Go to Data > Text to columns
- Chose “Delimited” in step 1 and click next.
- Specify delimiter as \
- Click Finish
- You will get all folders in to separate cells and file name in last cell.
- Now use a formula like =INDEX($C3:$O3,COUNTA($C3:$O3)) to extract the last cell’s value ie file name
- Done!
3. Using UDFs
While our formula method tends to be very long or very complicated, we can use 1-2 line VBA to get the file name from a full path. There are many ways to skin this cat in VBA, but 2 easiest methods are,For both methods below, you first need to insert a new module and add the code in that.
Using InStrRev
As suggested by Daniel Ferry in the comments.Public Function ParseFile(sPath As String) As Variant
ParseFile = Array(Mid$(sPath, 1 + InStrRev(sPath, “\”)), Mid$(sPath, 1 + InStrRev(sPath, “.”)))
End FunctionNote: this UDF returns an array for file name & extension. So you need to enter it in 2 cells together.
The InStrRev() built in function searches for \ in the sPath from end and returns the first occurrence’s position.
Using split
As suggested by PPH in comments,
Function ExtractFileName(filespath) As StringDim x As Variant
x = Split(filepath, Application.PathSeparator)
ExtractFileName = x(UBound(x))
End Function
What is your favorite method?
For most of my data cleaning needs, I use a mix of text to columns, find-replace or VBA. In rare cases, I rely on a formula. This is because data cleaning or extraction is usually one time step and figuring out a complex formula is not good idea in such cases.What about you? How do you go about extracting filenames, dates, numbers etc. buried in text? What method do you use often? Please share with us in comments.
More tips on Data Extraction:
- Extract numbers from text using formulas [and using macros]
- Extract dates from text with formulas & with text to columns
- Extract user names from email addresses
- Extract duplicates or unique values from a list
- Extract data from multiple files & place in one sheet
6 month access to our Online Classroom: Our online classroom is where all the students converge, learning from each other, discussing alternative ideas, sharing course notes,exploring homework solutions & networking with other Excel users. You can access our online classroom 24×7 for 6 months from the date you joined.
