Tuesday, January 22, 2008

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.

No comments: