Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    San Clemente, California, USA
    Posts
    130
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Extract data-left, right, med-from a cell(2002)

    I swear I've asked this question before, but for the life of me I can't find it!!

    Here is what I am trying to do:

    Example:
    Row 1: Adam Faith as Ronald 'Budgie' Bird in Budgie
    Row 2: Adam Sandler as Billy Madison in Billy Madison
    Row 3: Adam West as Bruce Wayne/Batman in Batman

    I would like to extract the following into seperate cells:
    Everything before the word 'as'
    Everthing between the words 'as' & 'in'
    Everything after the word 'in'

    I believe I have to create a formula using the mid and len functions but I don't know how~

    Thank you!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Extract data-left, right, med-from a cell(2002)

    Say your data start in A1.

    In B1, enter the formula <code>=LEFT(A1,FIND(" as ",A1)-1)</code>
    In C1, enter the formula <code>=MID(A1,FIND(" as ",A1)+4,FIND(" in ",A1)-FIND(" as ",A1)-4)</code>
    In D1, enter the formula <code>=MID(A1,FIND(" in ",A1)+4,100)</code>

    Select B11, then fill down as far as needed.

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extract data-left, right, med-from a cell(2002)

    If the string is in A, then the following formulas should give what you want:

    <code>
    =LEFT(A1,SEARCH(" as ",A1))
    =MID(A1,SEARCH(" as ",A1)+4,SEARCH(" in ",A1)-SEARCH(" as ",A1)-4)
    =RIGHT(A1,LEN(A1)-SEARCH(" in ",A1)-3)
    </code>
    Legare Coleman

  4. #4
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    San Clemente, California, USA
    Posts
    130
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Extract data-left, right, med-from a cell(2002)

    <img src=/S/sing.gif border=0 alt=sing width=24 height=20> Have I told you lately how much I love you <img src=/S/sing.gif border=0 alt=sing width=24 height=20>

    That is exactly what I needed and it worked beautifully..

    So Search & Find do the same thing? Are there any differences?

  5. #5
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    San Clemente, California, USA
    Posts
    130
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Extract data-left, right, med-from a cell(2002)

    Uh-oh....

    I just hit a variable:

    Charlton Heston as Maj. Matt Lewis, Ava Gardner as Baroness Natalie Ivanoff and David Niven as Sir Arthur Robertson in 55 Days at Peking

    Hmmmm......
    I would like to have
    55 Days at Peking
    Charlton Heston, Ava Gardner and David Niven
    Maj. Matt Lewis,Baroness Natalie Ivanoff and Sir Arthur Robertson

    I'm guessing I'm going to have to do these one's manually... Any other suggestions?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Extract data-left, right, med-from a cell(2002)

    Look up FIND and SEARCH in the Excel help. FIND is case sensitive, SEARCH isn't.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Extract data-left, right, med-from a cell(2002)

    If you have only a few of these, I'd do them manually. If you have a lot of them, you'd probably need a custom VBA function to handle all variations.

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Extract data-left, right, med-from a cell(2002)

    Here is a custom function that works in these examples. Add it to a module and then use:
    =ExtractEntries(a1,1)
    to get the title from A1

    =ExtractEntries(a1,2)
    to get the actors from A1

    =ExtractEntries(a1,3)
    to get the roles from A1

    Steve

    <pre>Option Explicit
    Function ExtractEntries(sAll As String, iType As Integer)
    Dim sExtract(1 To 3) As String
    Dim x As Integer
    Dim i As Integer
    Dim iCount As Integer
    Dim sTemp As String
    Dim sTemp2 As String
    Dim AWF As WorksheetFunction
    If iType < 1 Or iType > 3 Then
    ExtractEntries = CVErr(xlErrNum)
    Exit Function
    End If
    Set AWF = Application.WorksheetFunction
    x = InStr(sAll, " in ")
    sTemp = Left(sAll, x - 1) & ","
    sExtract(1) = AWF.Trim(Mid(sAll, x + 4))
    iCount = (Len(sTemp) - Len(AWF.Substitute(sTemp, " as ", ""))) / 4
    For i = 2 To 3
    sExtract(i) = ""
    Next
    sTemp = AWF.Substitute(sTemp, " and ", ", ", 1)
    For i = 1 To iCount
    x = InStr(sTemp, ",")
    sTemp2 = Left(sTemp, x - 1)
    sTemp = Mid(sTemp, x + 1)
    x = InStr(sTemp2, " as ")
    sExtract(2) = sExtract(2) & ", " & _
    AWF.Trim(Left(sTemp2, x - 1))
    sExtract(3) = sExtract(3) & ", " & _
    AWF.Trim(Mid(sTemp2, x + 4))
    Next
    For i = 2 To 3
    sTemp = Mid(sExtract(i), 3)
    x = (Len(sTemp) - Len(AWF.Substitute(sTemp, ", ", ""))) / 2
    If x > 0 Then
    sExtract(i) = AWF.Substitute(sTemp, ", ", " and ", x)
    Else
    sExtract(i) = sTemp
    End If

    Next
    ExtractEntries = sExtract(iType)
    Set AWF = Nothing
    End Function</pre>


  9. #9
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    San Clemente, California, USA
    Posts
    130
    Thanks
    2
    Thanked 0 Times in 0 Posts
    So it's about 3 years later and I found myself working on the same issue! Of course, the first thing I do is check out the Lounge and see how I did it last time.

    Steve... I didn't get notification of your response back in 2006, so I didn't see your wonderful response.

    I just implemented it and THANK YOU I know I am a little late in thanking you, but I really appreciate all your help.

    -Leslie

Posting Permissions

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