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.

are your data always like 11301-21? I mean, it's always 5 digit number - 2 digit number? — Foxfire And Burns And Burns, Mar 21 '18 at 11:34
No. There are item codes of various lengths. But every item code is accompanied by a strange character. If you paste the item code in my question thread above and see its length in excel it may give 9 or 10 characters when it should be 8. — Umar Ahmad, Mar 21 '18 at 11:39
Yes, I noticed that. Just asking to provide you a solution, because there is a way to extract only the numbers of this item code, getting the value `1130121`. Next step would be how to assemble again the numbers as item code again, with the same estructure. Are all your item codes like number - number? I mean, is there always a - in the item code and nothing else? — Foxfire And Burns And Burns, Mar 21 '18 at 11:42
Can you post several examples of item codes? Maybe we can try to fix something — Foxfire And Burns And Burns, Mar 21 '18 at 11:54
‭12-92003-53‬ ‭12-..1051A607‬ ‭12-1074F045‬ ‭12-1070F089‬ Hope it helps — Umar Ahmad, Mar 21 '18 at 11:58

Foxfire And Burns And Burns · Accepted Answer · 2018-03-27T08:59:38.990

2

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

edited Mar 27 '18 at 08:59

answered Mar 21 '18 at 12:28

Foxfire And Burns And Burns

10,665
2
17
38

Nice use of *Regex* – Gary's Student Mar 21 '18 at 12:38
Excellent! You guys nailed it! Thank you very much. I hope this will answer the questions of many who tried standard formulas like TRIM, Clean etc. A big thanks to both of you. Peace :) – Umar Ahmad Mar 21 '18 at 13:04
The ThisCell parameter should be `ByVal`, not `ByRef`. `ByRef` is confusing because it anticipates setting ThisCell—which you don't—with the effect of resetting the caller's variable—which is all that `ByRef` is for. Similarly for the txt parameter because in VBA `ByRef` is the default. – Tom Blodget Mar 21 '18 at 19:59
@TomBlodget Thx for the suggestions. Updated the code thanks to your help. – Foxfire And Burns And Burns Mar 21 '18 at 20:50
Dears, I feel this VBA code is not working after an update in the code. I couldn't get a copy of the previous code yesterday. So please restore the previous version or make the current code work. – Umar Ahmad Mar 22 '18 at 09:09
@UmarAhmad Posted the old one from yesterday. – Foxfire And Burns And Burns Mar 22 '18 at 09:57
@FoxfireAndBurnsAndBurns Please,I got another issue: Item code: WINHY–HUJ-CONT#12RN991-01 doesn't support your formula. It gives me the result by eliminating the first (-). Could you please improve your code so that it supports all item codes and eliminates just the stubborn characters. – Umar Ahmad Mar 25 '18 at 09:58
What would be the desired output in that case? A string 25 chars long? – Foxfire And Burns And Burns Mar 26 '18 at 08:13
Yes please. The above item code however does not have hidden chars. But I have it in my file. I need the code to clean and keep the item code as it is, rather than to change its shape. – Umar Ahmad Mar 26 '18 at 10:22
Sorry, i don't understand what you mean. *The above item code however does not have hidden chars. But I have it in my file* Without the problematic code, I cannot fix anything. – Foxfire And Burns And Burns Mar 26 '18 at 10:33
‭WIHTG–XYH-CONT#16AR999-01‬ please take this item code to work out. it has the problem. shows 27 chars instead of 25. – Umar Ahmad Mar 26 '18 at 11:22
It shows 26 for me, and 24 after applying formula. I get `WIHTGXYH-CONT#16AR999-01` after applying formula – Foxfire And Burns And Burns Mar 26 '18 at 11:32
You mean you want to keep the `–` ? – Foxfire And Burns And Burns Mar 26 '18 at 11:34
Yes. I want to keep the (-) please. – Umar Ahmad Mar 27 '18 at 05:24
Perfect now! Thank you! – Umar Ahmad Mar 27 '18 at 11:43

Gary's Student · Answer 2 · 2018-03-21T12:25:26.643

1

You have characters that look like a space character, but are not. They are UniCode 8236 & 8237.

Just replace them with a space character (ASCII 32).

EDIT#1:

Based on the string in your post, the following VBA macro will replace UniCode characters 8236 amd 8237 with simple space characters:

Sub Kleanup()
Dim N1 As Long, N2 As Long
Dim Bad1 As String, Bad2 As String

N1 = 8237
Bad1 = ChrW(N1)
N2 = 8236
Bad2 = ChrW(N2)

Cells.Replace what:=Bad1, replacement:=" ", lookat:=xlPart
Cells.Replace what:=Bad2, replacement:=" ", lookat:=xlPart

End Sub

edited Mar 21 '18 at 12:25

answered Mar 21 '18 at 11:56

Gary's Student

95,722
10
59
99

Can you please give me an example? – Umar Ahmad Mar 21 '18 at 12:00
@UmarAhmad See my **EDIT#1** – Gary's Student Mar 21 '18 at 12:25
The space character is U+0020—VBA doesn't use ASCII. – Tom Blodget Mar 21 '18 at 20:02

hidden space in excel

2 Answers2