Using Len with Left (or Mid) in VBA

I have a file that looks like this:

And what I need to do is to remove every individual "NA" from the range, using VBA. Without shifting cells.

I can’t do (replace), because it’s possible that there could be "na" in the other strings.
I assume "Instr" won’t work for the same reason.

I tried a couple methods, which I thought would work, but for the reasons I can’t understand, they don’t.

At first I tried Left + Len, like this:

dim nb as workbook

for i = 2 to nb.sheets(1).cells(rows.count, 17).end(xlup).row

if left(range("Q" & i).value, 2, "NA", Len(range("Q" & i).value)) then
nb.sheets(1).range("Q" & i).value = ""

end if
next i

When that did not work, I tried this:

for i = 2 to nb.sheets(1).cells(rows.count, 17).end(xlup).row

if mid(range("Q" & i).value, 0 + 2, Len(range("Q" & i).value)) And _
Instr(1, (range"Q" & i).value), "NA") then
nb.sheets(1).range("Q" & i).value = ""

end if
next i

I also tried many other ways to rearrange Len, Left and Mid, but none worked.

And even though the solution doesn’t have to be done using Left (mid) and Len, is there a way to do it this way? And if not, what other ways there are?

>Solution :

You can use Replace if you specify Lookat:=xlWhole

With nb.Sheets(1)
    .Range("Q2:U" & .Cells(.Rows.Count,"Q").End(xlUp).Row) _
    .Replace "NA", "", Lookat:=xlWhole, MatchCase:=True
End With