Search for Specific Text within a Cell

[K_Barnett]

Hello all,

Capture.JPG
My main objective here is to make sure that Columns A and B agree with the "Rev" level here. I have about 11k rows to sift through. I am wondering how to search for this specific text (REV) in column A and assign the correct letter to a variable.
For example if we take the first row in the picture I would search "Rev" (this part is easy for me) and see that for this row, it does contain that word. Then (and this is the hard part for me) I would need to assign the letter(s) following "REV" to a variable to compare it to the value found in the corresponding row in column B. Can you assign specific text within a cell to a String? I know a fair bit so I don't need help with the searching, just how i would assign specific text within a cell to a variable for comparison purposes. Thanks for any help.

[SamT]

Can you just assign "Rev A" to column B regardless of what is currently in B?

Sub ReplaceRevision()
Dim Chars As Long
Dim Cel As Range
Dim ColA As Range

Set ColA = Range(Cells(2, "A"), Cells(Rows.Count, "A").End(xlUp))

For Each Cel In ColA
  Chars = InStrRev(Cel, " ")
  Chars = Len(Cel) - Chars
  Cel.Offset(, 1) = "Rev " & Right(Cel, Chars)
Next
End Sub