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

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

4. ## 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. ## 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. ## 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. ## 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. ## 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. 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
•