Remove trailing commas and spaces without changing text color format

Hi,

I have found below function which removes trailing comma and spaces but it removes my text formatting too.

Function removetrailcomma(txt As String) As String
    If Right(txt, 1) = " " Or Right(txt, 1) = "," Then
        removetrailcomma = removetrailcomma(Left(txt, Len(txt) - 1))
    Else
        removetrailcomma = txt
    End If
End Function

Can some one please help!, as I have thousand's of rows where I have to remove trailing commas and spaces while keeping the text format as it is.

Ex: Current Cell - Peter(In black font), 1st Rank(In red font),
Current Code result - Peter(In black font), 1st Rank(In black font)
Desired result - Peter(In black font), 1st Rank(In red font)

I have attached a sample file too!..