How to trim last two characters from a cell? (Not standard spaces)

[datanewb]

I found this snippet on the web to trim the leading and trailing spaces:

Sub TrimRText()
' This module will trim extra spaces from RIGHT SIDE.
    Dim MyCell As Range
    On Error Resume Next
        Selection.Cells.SpecialCells(xlCellTypeConstants, 23).Select
        For Each MyCell In Selection.Cells
            MyCell.Value = Application.Trim(MyCell.Value)
        Next
    On Error GoTo 0
End Sub

However, stepping through it I found it not to work. I think this is because the characters are not standard spaces. (What exactly they are, I don't know).

The good news is, there is always two of these characters on the right side of the value that needs to be trimmed. Unfortunately, I am unfamiliar with VBA. How would I trim the last two characters from MyCell?

In c++ parlance:
MyCell = MyCell.substr(MyCell.begin(),MyCell.end()-2);

Thank you.

 

[pgc01]

Hi

When you read text from the net (html docs) sometimes you get a html non breaking space, character 160.

Try replacing the characters 160 by ascii spaces (character 32) and then you can use Trim().

 

[JackDanIce]

Try:

Sub HaircutAndSides()
Dim cell As Range
Application.ScreenUpdating = False
For Each cell In Cells.SpecialCells(xlCellTypeConstants, 23)
    If Right(cell, 2) = "AA" Then cell.Value = Left(cell, Len(cell) - 2)
Next cell
Application.ScreenUpdating = True
End Sub

Replace AA with whatever the 2 charachters to the right are that you want removed

 

[datanewb]

Thank you! I was overlooking the easiest solution of all. Replace All " " with "".