Create macro on excel generated reports

Here's the additon to remove the +500 values. I don't have a solution for you for adding the items as you don't have a normalized data sheet.

Option Explicit

Sub cleanup()
    Dim i As Long, lr As Long
    lr = Range("E" & Rows.Count).End(xlUp).Row
    For i = 5 To lr
        If InStr(Range("E" & i), "Km") > 0 Then
            Range("E" & i) = Left(Range("E" & i), Len(Range("E" & i)) - 3)
        End If
    Next i
    
    For i = 5 To lr
        If Range("E" & i) >= 500 Then
         Range("E" & i).ClearContents
        End If
    Next i
    MsgBox "Finished"
End Sub