Testing to find last word in a string

[mbpress01]

Thanks in advance for any advice. My issue is I am trying to find a way to first extract the last word in a string depending on what is in that last word. For example, the strings are option symbols as follows

IBM US 05/18/18 P40
IBM US 05/18/18 C140
IBM US 05/18/18 C14.5
IBM US 05/18/18 P20.75
IBM US 05/18/18 P150.50

What i need to do is to be able to test the last 3 to 6 characters of the string as follows:

P40
C140
C14.5
P20.75
P150.50

and if the any of the characters contains a "P", perform some action on another cell. I have the loop working so no real issue there but I can't figure out the right function/formula to make this work.

Here is what i have so far

Dim x As Long
With Worksheets("Test")
For x = 2 To Cells(Rows.Count, 2).End(xlUp).Row
If Cells(x, 2) = Formula.Trim(Right(Substitute(B4, " ", Rept(" ", 100)), 100)) Then 'this is the problem - this only extracts the last word so it doesn't work
Cells(x, 4) = Cells(x, 4) * -1
End If
Next x
End With

If I can figure out the function/formula then I can perform the action.

If anyone has any suggestions, I would appreciate it greatly

Thanks

 

[Scott T]

Does this do what you want?

Dim x As Long
 
 With Worksheets("Test")
    For x = 2 To Cells(Rows.Count, 2).End(xlUp).Row
           
        If InStr(Trim(Right(Replace(Cells(x, 2), " ", Application.Rept(" ", 200)), 200)), "P") <> 0 Then 
            Cells(x, 4) = Cells(x, 4) * -1
        End If
    Next x
 End With

 

[Fluff]

Another option

   Dim Cl As Range
   With Worksheets("Test")
      For Each Cl In .Range("B2", .Range("B" & Rows.Count).End(xlUp))
         If Left(Split(Cl.Value, " ")(UBound(Split(Cl.Value, " "))), 1) = "P" Then
            Cl.Offset(, 2).Value = Cl.Offset(, 2) * -1
         End If
      Next Cl
   End With

 

[mbpress01]

Thanks to both for all the assitance. Fluff I did get an Application defined error on the

If Left(Split(Cl.Value, " ")(UBound(Split(Cl.Value, " "))), 1) = "P" Then line

that I will need to diagnose. But thanks as always as I will be able to get it to work after I figure out what it is doing.

 

[Fluff]

What was the error message & what was the value of Cl when it failed?