Remove Trailing Semicolons

[dfolzenlogen]

I found the following formula for removing trailing commas on this site:

Sub RemoveTrailingCommas_Version1()
Dim Cell As Range
For Each Cell In Selection
Cell.Value = Replace(Replace(RTrim(Replace(Replace(Cell.Value, " ", Chr(1)), ",", " ")), " ", ","), Chr(1), " ")
Next
End Sub


I want to remove trailing semicolons so I changed the formula to read as follows:

Sub RemoveTrailingSemicolon()
Dim Cell As Range
For Each Cell In Selection
Cell.Value = Replace(Replace(RTrim(Replace(Replace(Cell.Value, " ", Chr(59)), ";", " ")), " ", ";"), Chr(59), " ")
Next
End Sub

Unfortunately, the above formula is removing all other semicolons in the cell which is separating other data. What am I missing?

Thanks.

 

[Peter_SSs]

Leave Chr(1) as it was.

 

[Peter_SSs]

.. and other options would be

Sub RemoveTrailingSemicolon_v2()
  Dim Cell As Range
  
  With CreateObject("VBScript.RegExp")
    .Pattern = ";+$"
    For Each Cell In Selection
      Cell.Value = .Replace(Cell.Value, "")
    Next Cell
  End With
End Sub

Sub RemoveTrailingSemicolon_v3()
  Dim Cell As Range
  Dim i As Long
  
  For Each Cell In Selection
    i = Len(Cell.Value) + 1
    Do While Mid("x" & Cell.Value, i, 1) = ";"
      i = i - 1
    Loop
    Cell.Value = Left(Cell.Value, i - 1)
  Next Cell
End Sub

 

[dfolzenlogen]

Thank you Peter!!!

 

[Peter_SSs]

Thank you Peter!!!

Glad to help.