Excel2011 VBA Trimming String with invisible characters for PivotTable

Question

I'm a new member of your community, my names is Thomas. I used to be a VisualFoxPro Developper years ago, but for now I'm trying to learn how to handle Excel (2011 on a Mac) and VBA. Only on my sparetime ;-). I have googled a lot since I started and really often found the answer right here, so here I come.

Here is the (almost) whole picture : I'm retreving datas from HTML files (Bridge competition results), at some point (row) there's fixed column datas and one column contains 2 Player's names seperated by " - ". I have no problem to retrieve the first one but can't trim the second one. I have tried, TRIM, CLEAN, SUBSTITUE with all CHAR -> 31 and CHAR 127, 129, 141, 143, 144 and 157. I even try this, found somewhere on google :

Sub Clean_and_Trim_Cells()

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Dim s As String
    For Each c In ActiveSheet.UsedRange
        s = c.Value
        If Trim(Application.Clean(s)) <> s Then
            s = Trim(Application.Clean(s))
            c.Value = s
        End If
    Next
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

So, for the same player, I end up with different lenght of string one who is properly trimed and another one longer. That I don't Understand.

And Here is how I define the lenght of the columns and extract names.

Sub PieceOfCode

    If Left(Trim(cDataString), 4) = "Rang" Then      ‘Header row

        'Here we define the columns width and initiate data collection
        nScore = 0
        cTeamName = ""
        cTeam = ""
        cP1 = ""
        cP2 = ""
        nCol1 = InStr(cDataString, "Rang")
        nCol2 = InStr(cDataString, "Paire")
        nCol3 = InStr(cDataString, "Score")
        nCol4 = InStr(cDataString, "Nom")
        nCol5 = InStr(cDataString, "Equipe")
        nCol1Length = nCol2 - 1
        nCol2Length = nCol3 - nCol2
        nCol3Length = nCol4 - nCol3
        nCol4Length = nCol5 - nCol4
        nCol5Length = Len(cDataString) - nCol5 + 1

        'And further in the process...

        nScore = Val(Mid(Replace(cDataString, ",", "."), nCol3, nCol3Length))
        cNamesString = Mid(cDataString, nCol4, nCol5 - nCol4)
        nSplitTeamNamesPos = InStr(cNamesString, " - ")
        cP1 = Trim(Mid(cNamesString, 1, nSplitTeamNamesPos - 1))
        cP2 = Trim(Mid(cNamesString, nSplitTeamNamesPos + 3))

    End If

End Sub

When I build a PivotTable with this I get 2 entries for the same player.

Here the code I have to create the pivot table, I confess, with the help of the recorder, but even if that's why, I'd know how to trim my string.

Sub CreatePivotTable()

    Dim nRowCount As Integer
    Dim cRowCount As String

    Sheets("Data").Select
    Range("A1").Select
    nRowCount = GetRowCount("Data")
    cRowCount = Trim(CStr(nRowCount))

    Range("A1:B" & cRowCount).Select

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "Data!R1C1:R" & cRowCount & "C2", Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="TempPvt!R1C1", TableName:="PivotTable8", DefaultVersion _
    :=xlPivotTableVersion14

    Sheets("TempPvt").Select

    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable8").PivotFields("Nom")
        .Orientation = xlRowField
        .Position = 1
    End With

    ActiveSheet.PivotTables("PivotTable8").AddDataField ActiveSheet.PivotTables( _
    "PivotTable8").PivotFields("Score"), "Total", xlSum
    ActiveSheet.PivotTables("PivotTable8").AddDataField ActiveSheet.PivotTables( _
    "PivotTable8").PivotFields("Score"), "Sum of Score2", xlSum
    ExecuteExcel4Macro _
    "PIVOT.FIELD.PROPERTIES(""PivotTable8"",""Sum of Score2"",""# Tours"",,4)"
    ActiveSheet.PivotTables("PivotTable8").AddDataField ActiveSheet.PivotTables( _
    "PivotTable8").PivotFields("Score"), "Sum of Score2", xlSum
    ExecuteExcel4Macro _
    "PIVOT.FIELD.PROPERTIES(""PivotTable8"",""Sum of Score2"",""Moyenne"",,8)"

End Sub

And finally here is two strings with header for testing (BAPTISTE Claude is the target).

Rang Paire Score Nom Equipe

 302 1,83 Mme NIMSGERN Monique - BAPTISTE Claude Mme EMERIQUE

Rang Paire Score Nom Equipe

 8 101 0,92 BAPTISTE Claude - Mme NIMSGERN Monique Mme EMERIQUE

That's it.