Deleting Last Word in the Cell based on Condition

[madhuchelliah]

Hello all, i want to delete the last word in the cell if it is not equal "OK". My data is in A column and range is dynamic. The macro has look into all the cells in the column and delete the last word, if it is not equal to "OK". Thank you.

 

[Peter_SSs]

Try this with a copy of your workbook.

Sub Delete_Last_Word()
  With Range("A2", Range("A" & Rows.Count).End(xlUp))
    .Value = Evaluate(Replace("if(#="""","""",if(right(#,3)="" OK"",#,trim(left(#,len(#)-len(trim(right(substitute(#,"" "",rept("" "",50)),50)))))))", "#", .Address))
  End With
End Sub

 

[jolivanes]

A loop should do it if the data range is not too large

Sub With_A_Loop()
Dim c As Range
    For Each c In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
        If Mid(c, InStrRev(c, " ") + 1, 99) <> "OK" Then c.Value = Left(c, InStrRev(c, " ") - 1)
    Next c
End Sub

Peter is way ahead of me, both in time and excel intelligence with that code.

 

[Peter_SSs]

A loop should do it if the data range is not too large

Just a couple of comments

1. Your code will error if there are any blank cells or cells with only one word.
2. A shorter way of testing
   If Mid(c, InStrRev(c, " ") + 1, 99) <> "OK"
   would be
   If Right(c, 3) <> " OK"

 

[jolivanes]

You're right. Too much in a hurry.
Thanks Peter.

 

[madhuchelliah]

Try this with a copy of your workbook.

Sub Delete_Last_Word()
  With Range("A2", Range("A" & Rows.Count).End(xlUp))
    .Value = Evaluate(Replace("if(#="""","""",if(right(#,3)="" OK"",#,trim(left(#,len(#)-len(trim(right(substitute(#,"" "",rept("" "",50)),50)))))))", "#", .Address))
  End With
End Sub

Thanks Peter, it is working fine. Thanks for your time.

 

[madhuchelliah]

You're right. Too much in a hurry.
Thanks Peter.

Appreciate your efforts.

 

[Peter_SSs]

Thanks Peter, it is working fine. Thanks for your time.

You're welcome. Thanks for the follow-up.