Can you help me adjust this formula? Need to remove part of it if cirteria exists

[TWTHOMAS]

I am splitting employee names to use for filling forms.

Currently I have employee names in column A like this

Jones, Thomas J
Smith, Susan
Jackson, Michael E JR
Wilson, Dan SR

I use this formula to create the field that only contains the middle intial

=TRIM(MID(SUBSTITUTE($A2," ",REPT(" ",LEN($A2))),((COLUMNS($A2:C2)-1)*LEN($A2))+1,LEN($A2)))&"."

However when Susans field populates I get a period even though she doesn't have a middle inital.
So her form looks like

Susan . Smith

Is there an adjustment I can make so when there is no middle initial I don't get a period?

 

[Special-K99]

Maybe

=SUBSTITUTE(TRIM(MID(SUBSTITUTE($A2," ",REPT(" ",LEN($A2))),((COLUMNS($A2:C2)-1)*LEN($A2))+1,LEN($A2)))&".",".","")

Just substitute null for any full stops in the result of your formula.

 

[TWTHOMAS]

Maybe

=SUBSTITUTE(TRIM(MID(SUBSTITUTE($A2," ",REPT(" ",LEN($A2))),((COLUMNS($A2:C2)-1)*LEN($A2))+1,LEN($A2)))&".",".","")

Just substitute null for any full stops in the result of your formula.

This didn't work. It removes the period completely even if there is a middle initial.