Removing File Names From Paths

GreyEyedWolf

New Member
Joined
Jul 26, 2014
Messages
14
Hello, I was wondering if anyone had a good method for taking cells with file paths and removing the file names from them?

For example if I had the following path

C:\TEST\WORD.DOC I would want the cell to read C:\TEST to just contain the folder.

I have a long list of file paths with names and want to extract only the paths. Text to columns ends up being more work because some of the folder structures are very deep, so I would like an easier solution.

Thanks
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Excel 2012
ABCDE
1C:\TEST\WORD.DOCC:\TEST7< Proof
Sheet1
Cell Formulas
RangeFormula
B1=LEFT(A1,FIND("\",A1,4)-1)
D1=LEN(B1)
 
Upvote 0
If you want to fix it in place, you could use a run-once macro. Not well tested, so for sure, try in a copy of your workbook first...

In a Standard Module:

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br>  <br><SPAN style="color:#00007F">Sub</SPAN> RunOnce()<br><SPAN style="color:#00007F">Dim</SPAN> Cell <SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">Dim</SPAN> IndexSlash <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> IndexDot <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>  <br>  <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> Cell <SPAN style="color:#00007F">In</SPAN> Selection.Cells<br>    IndexSlash = InStrRev(Cell.Value, Application.PathSeparator)<br>    IndexDot = InStrRev(Cell.Value, ".")<br>    <br>    <SPAN style="color:#00007F">If</SPAN> IndexSlash > 0 And IndexDot > 0 <SPAN style="color:#00007F">Then</SPAN><br>      Cell.Value = Left$(Cell.Value, IndexSlash)<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>  <SPAN style="color:#00007F">Next</SPAN><br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

With the correct sheet active and the cells selected with the fullnames, you would run the code.

Mark
 
Upvote 0
If you want to fix it in place, you could use a run-once macro. Not well tested, so for sure, try in a copy of your workbook first...

In a Standard Module:

Option Explicit
**
Sub RunOnce()
Dim Cell As Range
Dim IndexSlash As Long
Dim IndexDot As Long
**
**For Each Cell In Selection.Cells
****IndexSlash = InStrRev(Cell.Value, Application.PathSeparator)
****IndexDot = InStrRev(Cell.Value, ".")
****
****If IndexSlash > 0 And IndexDot > 0 Then
******Cell.Value = Left$(Cell.Value, IndexSlash)
****End If
**Next
****
End Sub


With the correct sheet active and the cells selected with the fullnames, you would run the code.

Mark

Thanks for all the replies this is exactly what I was looking for. Something that would keep the cells in place whilst removing the file names. I'm guessing the only risk involved with this macro might be that it confuses a folder with a period for being a file? The only way around this I could see is to have an additional column with the category of FOLDER or FILE to filter down to only files. I don't know if there would be any software workaround for getting that metadata.

Thanks
 
Upvote 0
Assuming you are running Windows, I have not run into any glitches like this. This does assume that all the filenames have a dot in them, or that is, a file extension showing. Is that the case?
 
Upvote 0
Assuming you are running Windows, I have not run into any glitches like this. This does assume that all the filenames have a dot in them, or that is, a file extension showing. Is that the case?

I'll just have to be extra cautious when running the macro to ensure that the last chunk of data after the last "\" is a file and not a folder. For example you could in theory have a folder named Test.DOC that on the surface looks like a file but is in fact a folder. Obviously Excel wouldn't be able to differentiate between a file and folder since the cell is just containing text. This is where you would need another cell containing metadata to identify folders vs files or some other method. Thanks
 
Upvote 0
I think this resolves any ambiguity:

Code:
Sub GEW()
    Dim cell        As Range
    Dim s           As String

    For Each cell In Selection.Cells
        s = cell.Value2
        If Len(s) Then
            If Len(Dir(s)) Then cell.Value2 = Left(s, InStrRev(s, "\"))
        End If
    Next cell
End Sub
 
Upvote 0
Hi shg,

I am not entirely sure that the fullnames are from the same PC as where the code is being run, but hopefully :)

Mark
 
Last edited:
Upvote 0
Hi shg,

I am not entirely sure that the fullnames are from the same PC as where the code is being run, but hopefully :)

Mark

I was wondering, and sorry for asking a lot of questions. But do you think it would be possible to modify your macro so that it firstly deletes the file names, and then deletes the "\" afterwards. So for example C:\TEST\TEST.DOC results in C:\TEST\ afterwards. Is it possible to Delete the highlighted "\" so the result is just C:\TEST ? Thanks
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,101
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top