If you wanted to use the formula I posted, you could have it in an intermediary column, possibly hidden (your discrepancy) and use it something like this ...
Column B above would be hidden, then the formula in column C is ..
=IF(ISERR(B1),"",B1)
copy down as needed.
I also added a "Sr" into the function, as it didn't have it in the prior posting. Which would make it ...
=RIGHT(A1,LEN(A1)-FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-MIN(SUMPRODUCT((LEN(A1)-LEN(SUBSTITUTE(A1,{"Sr","Jr","III","IV","V","VI"},"")))),1))))&", "&SUBSTITUTE(A1," " &RIGHT(A1,LEN(A1)-FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-MIN(SUMPRODUCT((LEN(A1)-LEN(SUBSTITUTE(A1,{"Sr","Jr","III","IV","V","VI"},"")))),1)))),"")
You could also break this formula up into more than one cell in other ways, but the error check is my personal preference.
If you do not need the extreme flexibility of this solution, maybe the one Norie posted would suffice for you. Remember, if you only drive a Volvo, there's no (practical) need to gear it up as a Rolls Royce. Unless you are from Texas and like everything big. (j/k)