Here's a Quick Way to Separate a File Name from a Path in Microsoft Excel

It happened again. I was presented with a text file that contained a large list of full paths and I needed to separate the file name from the path.

I can't count the number of times I have had to rewrite this Excel macro over the years; this typically happens whenever I get a new laptop and forget to back up my Excel macro modules, but this time it's because I'm at a new job and my backups are on my external hard drive at home. It's a simple problem, but anything is annoying to have to do from scratch, so I'm finally going to document it here.

Here's what I came up with. This macro assumes you have selected the first cell in your list of files, and there is a blank row immediately following the last entry in your list. It will write the path to the column immediately to the right of the current cell and the file name two columns to the right of the current cell. If you have any data in these two columns, it will be overwritten - so make sure you insert two columns to the right of your file list if you need to.

Sub SeparateFileFromPath()

 'turn off screen updating to make macro run faster

 Application.ScreenUpdating = False

 
 Dim fullpath As String

 fullpath = ActiveCell.Value

 
 ' do this for the whole list of files

 Do While Len(fullpath) > 0

  Dim n As Integer

  Dim m As Integer

  n = InStrRev(fullpath, "\")

  m = Len(fullpath) - n

  'everything to the left of the last '\' is the path

  Dim path As String

  path = Left(fullpath, n)

  ' everything to the right of the last '\' is the filename

  Dim filename As String

  filename = Right(fullpath, m)

  ActiveCell.Offset(0, 1).Value = path

  ActiveCell.Offset(0, 2).Value = filename

  'move to the next row

  ActiveCell.Offset(1, 0).Activate

  fullpath = ActiveCell.Value

 Loop

 
 ' turn screen updating back on

 Application.ScreenUpdating = True

End Sub

Enjoy! If you have any improvements, let me know in the comments; I'll give you credit and make revisions.