Results 1 to 9 of 9

20060807, 20:07 #1
 Join Date
 Mar 2001
 Location
 San Clemente, California, USA
 Posts
 130
 Thanks
 2
 Thanked 0 Times in 0 Posts
Extract dataleft, right, medfrom 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!

20060807, 20:25 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Extract dataleft, right, medfrom 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.

20060807, 20:26 #3
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Extract dataleft, right, medfrom 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

20060807, 23:34 #4
 Join Date
 Mar 2001
 Location
 San Clemente, California, USA
 Posts
 130
 Thanks
 2
 Thanked 0 Times in 0 Posts
Re: Extract dataleft, right, medfrom 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?

20060807, 23:50 #5
 Join Date
 Mar 2001
 Location
 San Clemente, California, USA
 Posts
 130
 Thanks
 2
 Thanked 0 Times in 0 Posts
Re: Extract dataleft, right, medfrom a cell(2002)
Uhoh....
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?

20060808, 03:03 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Extract dataleft, right, medfrom a cell(2002)
Look up FIND and SEARCH in the Excel help. FIND is case sensitive, SEARCH isn't.

20060808, 03:14 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Extract dataleft, right, medfrom 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.

20060808, 12:31 #8
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Extract dataleft, right, medfrom 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>

20090428, 13:46 #9
 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