parts of macro aren't working

Hey all,

Parts of the below macro aren't working. For example, if a name is like this:

John Smith R, Sr.
Ann Smith S,


It's supposed to be replaced with:

John Smith R
Ann Smith S

Sub extractor()

Dim lngLastRow As Long

   lngLastRow = Range("A" & Rows.Count).End(xlUp).Row

   Range("B1:B" & lngLastRow).Formula = _
       "=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(RIGHT(A1,LEN(A1)-FIND(""vs."",A1&""vs."")-3),"", et al"",""""),""Defendant"",""""),"", Et Al"",""""),"" """""",""""),"" Jr."",""""),"" Jr"",""""),""Estate of"",""""))"


   Range("B1:B" & lngLastRow).Formula = _
       "=TRIM(IF(RIGHT(A1,3)=""MD"",LEFT(A1,LEN(A1)-3),IF(RIGHT(A1,5)="", Md"",LEFT(A1,LEN(A1)-5),IF(RIGHT(A1,3)="", III"",LEFT(A1,LEN(A1)-3),IF(RIGHT(A1,2)="","",LEFT(A1,LEN(A1)-2),IF(RIGHT(A1,2)=""V"",LEFT(A1,LEN(A1)-2),IF(RIGHT(A1,2)="", Sr."",LEFT(A1,LEN(A1)-2),A1)))))))"
   

   Range("B1:B" & lngLastRow).Value = Range("B1:B" & lngLastRow).Value

End Sub

It doesn't produce any kind of error message. It simply just doesn't get rid of aforementioned undesirable content, despite me clearing telling it to in the macro.

Thanks for response.