VBA to extract between 2 special character

[Wlk]

Hihi,
I need help in extracting a string between 2 special characters.

The string is "A086 > Windsor Yard - 7091 Smith Industrial Drive - 70155895"

Requirement:
"Windsor Yard" - Special characters are " >" and "-"

I got some parts of my VBA to work but im stuck on this one, not sure how to proceed.

'To separate product
sht.Range("o" & i).Value = WorksheetFunction.IfError(Left(sht.Range("n" & i), WorksheetFunction.Find(" -", sht.Range("n" & i)) - 1), sht.Range("n" & i))

'To separate site to yard and CSO =TRIM(MID(H2,FIND(">",H2)+1,FIND("-",H2)-FIND(">",H2)-1))

sht.Range("j" & i).Value = WorksheetFunction.IfError(Mid(sht.Range("i" & i), WorksheetFunction.Find("> ", sht.Range("i" & i)) + 1), sht.Range("i" & i))


Please help

Thanks

 

[Fluff]

How about

   Dim s As Long, e As Long
   With sht.Range("i" & i)
      s = InStr(1, .Value, ">")
      e = InStr(1, .Value, "-")
      sht.Range("j" & i).Value = Trim(Mid(.Value, s + 1, e - s - 1))
   End With

 

[Wlk]

Hey Fluff,
Thanks, i will try that. Thanks

 

[Wlk]

Qquick question, would i be able to achieve the result using the worksheetfunction.iferror.............something like the format below?

'To separate product
sht.Range("o" & i).Value = WorksheetFunction.IfError(Left(sht.Range("n" & i), WorksheetFunction.Find(" -", sht.Range("n" & i)) - 1), sht.Range("n" & i))

 

[Fluff]

Probably, but why use worksheet functions when there are perfectly good VBA functions, that should be a lot faster?

 

[Wlk]

because right now my vba looks like this, so want them to align

Application.Calculation = xlCalculationManual
Application.DisplayStatusBar = False
Application.EnableEvents = False
'Declaration of Variables
Dim PlantCol As Integer
Dim i As Integer
Dim wb As Workbook
Dim sht As Worksheet
Dim Price As Worksheet
Dim Plant As Worksheet
Set sht = Sheets("Raw")
Set Price = Sheets("Price")
Set Plant = Sheets("Plant")
For i = 2 To sht.Range("b1048576").End(xlUp).Row
 
    'To separate string Driver to Driver Name and EmployeeID
    sht.Range("c" & i).Value = sht.Range("b" & i).Value
    sht.Range("c" & i).Value = WorksheetFunction.IfError(Left(sht.Range("b" & i), WorksheetFunction.Find("3", sht.Range("b" & i)) - 1), sht.Range("b" & i))
    sht.Range("d" & i).Value = Right(sht.Range("b" & i), Len(sht.Range("b" & i)) - WorksheetFunction.Find(" 3", sht.Range("b" & i)))

 
    'Separate FuelEventDateTime to "dd-mmm-yyyy",mmm-yyyy","hh:ss"
    sht.Range("f" & i).Value = Format(sht.Range("e" & i).Value, "dd-mmm-yyyy")
    sht.Range("g" & i).Value = Format(sht.Range("e" & i).Value, "mmm-yyyy")
    sht.Range("h" & i).Value = WorksheetFunction.MRound(Format(sht.Range("e" & i).Value, "hh:mm"), 15 / 60 / 24) 'Rounded to the nearest 15 min
 
 
    'Lookup $ at Yard base on date range in Price sheet(Yard  - D column)
    If sht.Cells(i, sht.Range("o").End(xlToRight).Column) = "DIESEL" Then
    sht.Range("l" & i).Value = sht.Range("m" & i) * WorksheetFunction.VLookup(WorksheetFunction.EoMonth(sht.Range("E" & i), -1) + 1, Price.Range("a:e"), 2, False)

    ElseIf sht.Cells(i, sht.Range("o").End(xlToRight).Column) = "UNLEADED" Then
    sht.Range("l" & i).Value = sht.Range("m" & i) * WorksheetFunction.VLookup(WorksheetFunction.EoMonth(sht.Range("E" & i), -1) + 1, Price.Range("a:e"), 3, False)
 
    End If
 
   'To separate product
    sht.Range("o" & i).Value = WorksheetFunction.IfError(Left(sht.Range("n" & i), WorksheetFunction.Find(" -", sht.Range("n" & i)) - 1), sht.Range("n" & i))

  'To separate site to yard and CSO =TRIM(MID(H2,FIND(">",H2)+1,FIND("-",H2)-FIND(">",H2)-1))
 
  'To extract CSO
    sht.Range("k" & i).Value = Mid(sht.Range("i" & i), Len(sht.Range("i" & i)) - WorksheetFunction.Find("-", sht.Range("i" & i)))


    'count
    sht.Cells(i, WorksheetFunction.Max(Plant.Range("d:d")) - 10).Value = 1
    If i Mod 1000 = 0 Then DoEvents

 

[Fluff]

Please bear in mind that I have no idea where your data is, what the product yard & CSO are, or what you are trying to do with it.
Therefore I have no idea what your code is doing, or why.
You asked how to extract part of the string, which I answered. If that is not what you actually want, then I would suggest you explain what you do want.

 

[Wlk]

sure, thanks

 

[Rick Rothstein]

How about

   Dim s As Long, e As Long
   With sht.Range("i" & i)
      s = InStr(1, .Value, ">")
      e = InStr(1, .Value, "-")
      sht.Range("j" & i).Value = Trim(Mid(.Value, s + 1, e - s - 1))
   End With

Apparently the OP is looking for a differently constructed answer, but I just wanted to point out for future readers of this thread that all of the code (including the Dim statement) that Fluff posted above can be replaced by this single line of code...

Range("j" & i) = Trim(Split(Replace(Range("i" & i), ">", "-"), "-")(1))

 

[Wlk]

Hi Rick,
Thanks that works. I was in the rabbit hole researching different ways to do it.

My bad, I should be more specific.

Thanks both.