hidden space in excel

Question

I tried almost all the methods (CLEAN,TRIM,SUBSTITUTE) trying to remove the character hiding in the beginning and the end of a text. In my case, I downloaded the bill of material report from oracle ERP and found that the item codes are a victim of hidden characters.

After so many findings, I was able to trace which character is hidden and found out that it's a question mark'?' (via VBA code in another thread) both at the front and the end. You can take this item code‭: ‭11301-21‬

If you paste the above into your excel and see its length =LEN(), you can understand my problem much better.

I need a good solution for this problem. Therefore please help!

Thank you very much in advance.

Answer 1

Thanks to Gary's Student, because his answer inspired me.

Also, I used this answer for this code.

This function will clean every single char of your data, so it should work for you. You need 2 functions: 1 to clean the Unicode chars, and other one to clean your item codes_

        Public Function CLEAN_ITEM_CODE(ByRef ThisCell As Range) As String
    If ThisCell.Count > 1 Or ThisCell.Count < 1 Then
        CLEAN_ITEM_CODE = "Only single cells allowed"
        Exit Function
    End If


    Dim ZZ As Byte

    For ZZ = 1 To Len(ThisCell.Value) Step 1
        CLEAN_ITEM_CODE = CLEAN_ITEM_CODE & GetStrippedText(Mid(ThisCell.Value, ZZ, 1))
    Next ZZ


    End Function

    Private Function GetStrippedText(txt As String) As String
If txt = "–" Then
    GetStrippedText = "–"
Else
    Dim regEx As Object

    Set regEx = CreateObject("vbscript.regexp")
    regEx.Pattern = "[^\u0000-\u007F]"
    GetStrippedText = regEx.Replace(txt, "")
End If

End Function

And this is what i get using it as formula in Excel. Note the difference in the Len of strings:

Hope this helps