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?

Hi DK

Why not use DataText to columns in the menubar

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"DK" wrote in message oups.com...
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?

That would extract all the data which has \ as separator in the
columns. I just want 3rd and 4th. There is no specific length of the
text. If there had been, I would have used MID.

On Jun 13, 1:22 pm, "Ron de Bruin" wrote:
Hi DK

Why not use DataText to columns in the menubar

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm



"DK" wrote in ooglegroups.com...
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?- Hide quoted text -

- Show quoted text -

See Jim's reply

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"DK" wrote in message oups.com...
That would extract all the data which has \ as separator in the
columns. I just want 3rd and 4th. There is no specific length of the
text. If there had been, I would have used MID.

On Jun 13, 1:22 pm, "Ron de Bruin" wrote:
Hi DK

Why not use DataText to columns in the menubar

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm



"DK" wrote in ooglegroups.com...
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?- Hide quoted text -

- Show quoted text -

Jim,
Where are we specifying the column that would have the result? I don't
need it to show up in a Msg Box. I have about 35000 rows which have
the same kind of format. The File Path data is in Column D and the
data needs to be extracted in Col G & H.
On Jun 13, 1:41 pm, "Ron de Bruin" wrote:
See Jim's reply

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm



"DK" wrote in ooglegroups.com...
That would extract all the data which has \ as separator in the
columns. I just want 3rd and 4th. There is no specific length of the
text. If there had been, I would have used MID.

On Jun 13, 1:22 pm, "Ron de Bruin" wrote:
Hi DK

Why not use DataText to columns in the menubar

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

"DK" wrote in ooglegroups.com...
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?- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

For Each rCell in rngCol.Cells
var = VBA.Split(rCell.Text, "\", -1)
rCell.Offset(0, 3).Value = var(2)
rCell.Offset(0, 4).Value = var(3)
Next
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"DK"
wrote in message
Jim,
Where are we specifying the column that would have the result? I don't
need it to show up in a Msg Box. I have about 35000 rows which have
the same kind of format. The File Path data is in Column D and the
data needs to be extracted in Col G & H.

Another approach...

Sub tested()
Dim str As String
Dim var As Variant
str = "I:\Account_Documents\ABC\ABCXYZ\GOV Documents"
'zero based array is returned by the Split function.
var = VBA.Split(str, "\", -1)
MsgBox var(2) & vbCr & var(3)
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"DK"
wrote in message 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?

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