+ Reply to Thread
Results 1 to 3 of 3

Extracting specific data from one column to another column

  1. #1
    DK
    Guest

    Extracting specific data from one column to another column

    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.


  2. #2
    Tom Ogilvy
    Guest

    RE: Extracting specific data from one column to another column

    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.
    >
    >


  3. #3
    DK
    Guest

    Re: Extracting specific data from one column to another column

    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.
    > >
    > >



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1