VBA Code for MS Excel Formula to Extract before and after specified character

[OilEconomist]

Thanks in advance for your assistance.

I have a MS Excel formula that I would like to convert to VBA. For simplicity I have shortened the code. Yes I know I could just input B2 into the following formula instead of EntryVal1, but as I indicated, I shortened the code so, please understand. What I'm trying to do is extract a value after a character and before another character.

So for example, if B2 is as follows:
Bears vs. Doe, John Foster (SPC: 586982) (DOP: 04/05/1991)

I want C2 to be:
Doe, John Foster

normally, I would just use the following formula into C2, but I am doing something different "=MID(LEFT(A2, FIND(" (SPC: ",A2) -1), FIND("vs. ", A2) + 4, LEN(A2) )"

I am having trouble with the following line

EntryVal2 = "=MID(LEFT(" & """ & EntryVal1 & """ & ", FIND("" (SPC: ""," & """ & EntryVal1 & """ & ") -1), FIND(""vs. ""," & """ & EntryVal1 & """ & ") + 4, LEN(" & """ & EntryVal1 & """ & ") )"

Where my code is as follows

Sub ValGet()

Dim i as Long
Dim EntryVal1 as String
Dim EntryVal2 as String

For i = 2 to 10

EntryVal1 = Range("B" & i).Value
EntryVal2 = "=MID(LEFT(" & """ & EntryVal1 & """ & ", FIND("" (SPC: ""," & """ & EntryVal1 & """ & ") -1), FIND(""vs. ""," & """ & EntryVal1 & """ & ") + 4, LEN(" & """ & EntryVal1 & """ & ") )"

Range("C" & i).Formula = EntryVal2
Range("C" & i).Formula = Range("C" & i).Value

Next i


End Sub

 

[Fluff]

Try

EntryVal2 = "=MID(LEFT(" &  EntryVal1  & ", FIND("" (SPC: ""," &  EntryVal1  & ") -1), FIND(""vs. "","  & EntryVal1  & ") + 4, LEN("  & EntryVal1  & ") )"

 

[Fluff]

Another option, without the loop

With Range("C2:C10")
   .Value = Evaluate(Replace("MID(LEFT(@, FIND("" (SPC: "",@) -1), FIND(""vs. "",@) + 4, LEN(@) )", "@", .Offset(, -1).Address))
End With

 

[OilEconomist]

Try

EntryVal2 = "=MID(LEFT(" &  EntryVal1  & ", FIND("" (SPC: ""," &  EntryVal1  & ") -1), FIND(""vs. "","  & EntryVal1  & ") + 4, LEN("  & EntryVal1  & ") )"

Thanks so much @Fluff for the quick response. When I update with that code, I get the following error: "Runtime Error 1004 "Application-defined or Object-defined error" on the line:

Range("C" & i).Formula = EntryVal2

 

[Fluff]

In that case try

EntryVal2 = "=MID(LEFT(""" & EntryVal1 & """, FIND("" (SPC: "",""" & EntryVal1 & """) -1), FIND(""vs. "",""" & EntryVal1 & """) + 4, LEN(""" & EntryVal1 & """) )"

 

[OilEconomist]

In that case try

EntryVal2 = "=MID(LEFT(""" & EntryVal1 & """, FIND("" (SPC: "",""" & EntryVal1 & """) -1), FIND(""vs. "",""" & EntryVal1 & """) + 4, LEN(""" & EntryVal1 & """) )"

Thanks. That worked perfectly.

 

[bebo021999]

To extract a substring from A2:A10, just use VBA mid function:

Sub test()
Dim cell As Range
For Each cell In Range("A2:A10")
If cell <> "" Then
cell.Offset(, 1).Value = Trim(Mid(cell, InStr(cell, "vs.") + 4, InStr(cell, "(SPC:") - InStr(cell, "vs.") - 4))
End If
Next
End Sub

 

[Fluff]

Thanks. That worked perfectly.

You're welcome & thanks for the feedback.