I am trying to filter data from a directory listing in excel but not
too good at it ;o)

The task on hand is to extract the directory name / document class from
column C and display in Column H. The directory name for document class
are :

C or Corr or Correspondence - it needs to be shown in Column H as
Correspondence
A or Agree - it needs to be shown in Column H as Agreement
M or Memos - it needs to be shown in Column H as Memos
P or Pleadings - it needs to be shown in Column H as Pleadings

Now, the tricky part is that these appear at different places in each
cell. There is no specific location for each directory. But P, C, A,
Corr, Correspondence, Agree, Memo, Pleadings all are directories and
would have a backslash "\" preceeding or succeeding it.

P:\DAVID\00019\abc.wpd
P:\DAVID\00019\C\XYZ.wpd
P:\DAVID\00019\P\KLM.wpd
P:\DAVID\00019\Pleadings\ABCDEF.wpd
P:\DAVID\00019\POST\Correspondence\abc.wpd

Please help with a macro code.

Sub CodeData()
For Each cell In Range("C2:C20")
v = Split(cell.Value, "\")
s = ""
For i = LBound(v) To UBound(v) - 1

Select Case LCase(v(i))
Case "c", "correspondence"
s = "Correspondence"
Case "a", "agree"
s = "Agree"
Case "m", "memo", "memos"
s = "Memo"
Case "p", "pleading", "pleadings"
s = "Pleading"
End Select
If s < "" Then
cell.Offset(0, 5).Value = s
Exit For
End If
Next i
Next cell

End Sub

--
Regards,
Tom Ogilvy


"DK" wrote:

I am trying to filter data from a directory listing in excel but not
too good at it ;o)

The task on hand is to extract the directory name / document class from
column C and display in Column H. The directory name for document class
are :

C or Corr or Correspondence - it needs to be shown in Column H as
Correspondence
A or Agree - it needs to be shown in Column H as Agreement
M or Memos - it needs to be shown in Column H as Memos
P or Pleadings - it needs to be shown in Column H as Pleadings

Now, the tricky part is that these appear at different places in each
cell. There is no specific location for each directory. But P, C, A,
Corr, Correspondence, Agree, Memo, Pleadings all are directories and
would have a backslash "\" preceeding or succeeding it.

P:\DAVID\00019\abc.wpd
P:\DAVID\00019\C\XYZ.wpd
P:\DAVID\00019\P\KLM.wpd
P:\DAVID\00019\Pleadings\ABCDEF.wpd
P:\DAVID\00019\POST\Correspondence\abc.wpd

Please help with a macro code.

Hi Tom,
Works superb! Thanks a ton!
Regards
Dilpreet
Tom Ogilvy wrote:

Sub CodeData()
For Each cell In Range("C2:C20")
v = Split(cell.Value, "\")
s = ""
For i = LBound(v) To UBound(v) - 1

Select Case LCase(v(i))
Case "c", "correspondence"
s = "Correspondence"
Case "a", "agree"
s = "Agree"
Case "m", "memo", "memos"
s = "Memo"
Case "p", "pleading", "pleadings"
s = "Pleading"
End Select
If s < "" Then
cell.Offset(0, 5).Value = s
Exit For
End If
Next i
Next cell

End Sub

--
Regards,
Tom Ogilvy


"DK" wrote:

I am trying to filter data from a directory listing in excel but not
too good at it ;o)

The task on hand is to extract the directory name / document class from
column C and display in Column H. The directory name for document class
are :

C or Corr or Correspondence - it needs to be shown in Column H as
Correspondence
A or Agree - it needs to be shown in Column H as Agreement
M or Memos - it needs to be shown in Column H as Memos
P or Pleadings - it needs to be shown in Column H as Pleadings

Now, the tricky part is that these appear at different places in each
cell. There is no specific location for each directory. But P, C, A,
Corr, Correspondence, Agree, Memo, Pleadings all are directories and
would have a backslash "\" preceeding or succeeding it.

P:\DAVID\00019\abc.wpd
P:\DAVID\00019\C\XYZ.wpd
P:\DAVID\00019\P\KLM.wpd
P:\DAVID\00019\Pleadings\ABCDEF.wpd
P:\DAVID\00019\POST\Correspondence\abc.wpd

Please help with a macro code.