On Wed, 13 Jun 2007 10:16:01 -0700, DK wrote:

I have extracted a directory listing like:
I:\Account_Documents\ABC\ABCXYZ\GOV Documents

I want to extract the third directory (ABC) into one column and fourth
directory (ABCXYZ) in another column from the whole.

How can I do this quickly?

Here's yet another approach. It "works on" a range of Selected Cells, and puts
the "extracts" in the adjacent two columns:


===================================
Option Explicit
Sub Extract()
Dim c As Range
Dim oRegex As Object
Dim mcMatchCollection As Object

Set oRegex = CreateObject("VBScript.RegExp")
With oRegex
.Global = True
.IgnoreCase = True
.Pattern = "[\\]([^\\]*)"
End With

For Each c In Selection
With c
.Offset(0, 1).Clear
.Offset(0, 2).Clear
If oRegex.Test(.Text) = True Then
Set mcMatchCollection = oRegex.Execute(.Text)
.Offset(0, 1).Value = mcMatchCollection(1).SubMatches(0)
.Offset(0, 2).Value = mcMatchCollection(2).SubMatches(0)
End If
End With
Next c

End Sub
==================================
--ron