In this example, I'm wanting to remove the end of a string if it contains either bk, BK, Bk or bK. Is there a simple way to make this case insentitive? Thanks
If Right(Cells(i, 1), 2) = "bk" Then
Cells(i, 1) = Replace(Cells(i, 1), "bk", "")
End If
Yes, since you want to replace all sorts of "bk" at the end of the string, you can use UCase, like in the code below:
If UCase(Right(Cells(i, 1), 2)) = "BK" Then
Cells(i, 1) = Replace(Cells(i, 1), "bk", "", 1, -1, vbTextCompare)
End If
Note: adding the last parameter of vbTextCompare to the Replace function indicates that it's case-insensitive, and will remove all sorts of "bk".
Edit 1: In case you have multiple occurrences of "bk" in a certain string, and you only want to remove the one at the end of the string, use the code below (thanks to @Ron Rosenfeld):
If UCase(Right(Cells(i, 1), 2)) = "BK" Then
Cells(i, 1) = Left(Cells(i, 1), Len(Cells(i, 1)) - 2)
End If
bk's in the string and not just the one at the end.To remove only the last two characters, I suggest:
If UCase(R) Like "*bk" Then R = Left(R, Len(R) - 2)
where R is a range object (or text string)
