Code Help

[Ks1102]

Experts
Have good day, kindly help me the part of my code below , how do I get the middle words if I loop the criteria for example :

LR = Range("C" & Rows.Count).End(xlUp).Row
For R = 1 To LR
If Cells(R, 4).Value Like "*" & " STATUS CODE" & "*" Then  ( the unique string is “STATUS CODE 92 Stay# 06 “)
Sheet2.Cells(IndexI, 2).Value = “how do I get only after code (XX) form the wording”
End If
Next R

 

[Sixthsense]

Not sure what you are trying to do in the below statements

Then  ( the unique string is “STATUS CODE 92 Stay# 06 “)
Sheet2.Cells(IndexI, 2).Value = “how do I get only after code (XX) form the wording”