Detect hidden characters in VBA

[YasserKhalil]

Hello everyone
I am working on some data and by accident when copying a part cell I found there is a hidden character at the end
Attached a file .. the last word is in red and it is followed by hidden character and I used Code function to see what is it and I got 254

My question is how to detect if there are any hidden characters ..? and returns those characters so as to try to deal with them

[HansV]

The formula =CODE(RIGHT(A1,1)) returns 63 when I open your workbook, and the formula =FIND(CHAR(254),A1) returns #VALUE! indicating that character #254 is not found...

S2461.png

So I don't understand your problem.

[Doc.AElstein]

Hi Yasser,

I see 254 initially: 254 Inishilly.JPG : https://imgur.com/JafdWIz" onclick="window.open(this.href);return false;
But as soon as I do anything it changes to like what Hans sees
65 Wots that then.jpg : https://imgur.com/7XKnTIw" onclick="window.open(this.href);return false;

Anyway…..
I am not sure if “hidden” is the correct way to describe it. You may already have checked what that character 254 is? : -
https://theasciicode.com.ar/extended-as ... e-254.html" onclick="window.open(this.href);return false;
I expect it is a character, like any other. If you have it in your string then you have it in your string. I think some characters will display on whatever it is you are trying to look at it on, and some characters won’t display on whatever it is you are trying to look at it on.
If you take a look at what Lisa just posted here http://www.eileenslounge.com/viewtopic. ... 85#p243665" onclick="window.open(this.href);return false; , then you can see that you can get with VBA the character from a code line like
Asc(MySingleCharacter)

But I am not sure how it can be used to apply to your string of characters. Your characters are like something I don’t know what, . Arabic I guess??
If I try Asc( ) then I get mostly the number 63 back which is for the ? https://theasciicode.com.ar/ascii-print ... de-63.html" onclick="window.open(this.href);return false;

I am not sure if anyone can help who does not have your language of Excel installed.
But maybe this will give you an idea..
Lets say I have a string like
“AllowedCharactars” & Chr(254)

Now run this code:

Code: Select all

Sub WotchaGot()
Dim strYas As String
 Let strYas = "AllowedCharactars" & Chr(254)
Dim Lenf As Long: Let Lenf = Len(strYas)
Dim cnt As Long
    For cnt = 1 To Lenf
    Dim MySingleCarrot As String
     Let MySingleCarrot = Mid(strYas, cnt, 1)
     Debug.Print Asc(MySingleCarrot)
    Next cnt
End Sub

You get this in the Immediate window
65
108
108
111
119
101
100
67
104
97
114
97
99
116
97
114
115
254

That is just an example. Maybe it is possible to do the same for Arabic characters?? I don’t know. But if you could, then you could apply it to a solution of this form

Code: Select all

Sub WotchaGotWotchShuntHave()
Dim strYas As String
 Let strYas = "AllowedCharactars" & Chr(254)
Dim Lenf As Long: Let Lenf = Len(strYas)
Dim cnt As Long
    For cnt = 1 To Lenf
    Dim MySingleCarrot As String
     Let MySingleCarrot = Mid(strYas, cnt, 1)
    ' You must put into array all allowed characters in Arabic??
   Dim arrAllad() As Variant: Let arrAllad() = Array(65, 108, 111, 119, 101, 100, 67, 104, 97, 114, 99, 116, 115)
    Dim resMtch As Variant
     Let resMtch = Application.Match(Asc(MySingleCarrot), arrAllad(), 0)
        If IsError(resMtch) Then MsgBox prompt:="This character is not alllaowd  " & Asc(MySingleCarrot)
    Next cnt
End Sub

Maybe you can do something similar for Arabic ??
http://jrgraphix.net/r/Unicode/0600-06FF" onclick="window.open(this.href);return false;

Maybe my idea is very stupid for Arabic…

Alan

[LisaGreen]

Yasser,

Do you want to detect them or would you just like to get rid of them?

Lisa

[YasserKhalil]

Thanks a lot for replies
I tried the first code and this is the result I got in immediate window

Code: Select all

 65 
 108 
 108 
 111 
 119 
 101 
 100 
 67 
 104 
 97 
 114 
 97 
 99 
 116 
 97 
 114 
 115 
 254

so 254 is existing for me
and when copying the name in A1 to the immediate window I got this

Untitled.png

and the hidden character is there

What I need is a UDF that detects any hidden characters and I will use replace to remove them ...

[LisaGreen]

Do you have the codes/numbers for the characters you want to delete and the characters you want to not delete??
If you do, What are they please?


Lisa

[YasserKhalil]

As for the example attached the only hidden character is the last which is 254 as listed in the immediate window for me

[LisaGreen]

Are there any more characters you don't want? Do you have a list? Can you list the characters you will "accept" please?

Lisa

[Doc.AElstein]

Hi Yasser,
What does my first code do if you replace
Let strYas = "AllowedCharactars" & Chr(254)
With
Let strYas = Range("A1").Value

Alan

[YasserKhalil]

I got the same result as I told you. I am sure of that code that appears for me and I am sure it is a hidden character .. I can also remove it easily using replace
The problem for me or the point I am seeking for is how to detect any hidden character within the cell (any hidden) so as to deal with those hidden characters later

[Doc.AElstein]

Hi Yasser

What does my first code do if you replace
Let strYas = "AllowedCharactars" & Chr(254)
With
Let strYas = Range("A1").Value

Sub WotchaGot()
Dim strYas As String
Let strYas = Range("A1").Value

Code: Select all

 Sub WotchaGot()
Dim strYas As String
Let strYas = Range("A1").Value
Dim Lenf As Long: Let Lenf = Len(strYas)
Dim cnt As Long
    For cnt = 1 To Lenf
    Dim MySingleCarrot As String
     Let MySingleCarrot = Mid(strYas, cnt, 1)
     Debug.Print Asc(MySingleCarrot)
    Next cnt
End Sub

[YasserKhalil]

Yes Mr. Alan
I did the code and I got the asc codes for each character and I found 254 at the last as I told you

[Doc.AElstein]

No, you showed results for Let strYas = "AllowedCharactars" & Chr(254)
http://www.eileenslounge.com/viewtopic. ... 09#p243684" onclick="window.open(this.href);return false;
65
108
108
111
119
101
100
67
104
97
114
97
99
116
97
114
115
254
_.__________________________________________

Please run code http://www.eileenslounge.com/viewtopic. ... 89#p243708" onclick="window.open(this.href);return false; and show results for that code ( Let strYas = Range("A1").Value )
Alan
( P.s. Here are my results for Let strYas = Range("A1").Value: MyResults for Range( A1 ) Value.JPG : https://imgur.com/okBZkyS" onclick="window.open(this.href);return false;
63
63
63
63
32
63
63
63
63
32
63
63
63
63
32
63
63
63
63
63
32
63
63
63
63
63
63
63
63

[LisaGreen]

Would a simple conditional format work for you Yasser?

Lisa

[YasserKhalil]

This is the code I run

Code: Select all

Sub WotchaGot()
Dim strYas As String
Let strYas = Range("A1").Value
Dim Lenf As Long: Let Lenf = Len(strYas)
Dim cnt As Long
    For cnt = 1 To Lenf
    Dim MySingleCarrot As String
     Let MySingleCarrot = Mid(strYas, cnt, 1)
     Debug.Print Asc(MySingleCarrot)
    Next cnt
End Sub

and this is the result I got

Code: Select all

 227 
 209 
 237 
 227 
 32 
 213 
 200 
 205 
 236 
 32 
 218 
 200 
 199 
 211 
 32 
 199 
 225 
 211 
 237 
 207 
 32 
 199 
 225 
 222 
 225 
 199 
 230 
 236 
 254 

[Doc.AElstein]

Hi Yasser
OK, so going back to here:
http://www.eileenslounge.com/viewtopic. ... ad#p243676" onclick="window.open(this.href);return false;
Please read that all again.

I will try to explain again the idea I had/ have.

You now see that you have only some of your Arabic normal allowed characters
227
209
237
227
32
213
200
205
236
32
218
200
199
211
32
199
225
211
237
207
32
199
225
222
225
199
230
236

These are OK. These you wont to allow. They are good. They are not hidden

Now in my second code http://www.eileenslounge.com/viewtopic. ... ad#p243676" onclick="window.open(this.href);return false; Sub WotchaGotWotchShuntHave
arrAllad() is for all allowed characters ….. YOU MUST DO THIS
Let arrAllad() = Array(227, 209, 237 …………………………………………………………)

You must put all allowed ascii codes in arrAllad()
You must find all allowed arabic characters. I cannot do this for you. I do not have Arabic Excel ( I have German and English Excel )
This is/ was my idea…..

( .... Maybe alternative.. arrAllad() can be spreadsheet range? )
_.______________

Maybe I can take idea a little further:

Code: Select all

 Sub RebuildStringArabic()
Dim strYas As String
Let strYas = Range("A1").Value
Dim Lenf As Long: Let Lenf = Len(strYas)
Dim cnt As Long
    For cnt = 1 To Lenf
    Dim MySingleCarrot As String
     Let MySingleCarrot = Mid(strYas, cnt, 1)
    ' You must put into array all allowed characters in Arabic.................. ..... YOU MUST DO THIS ......
   Dim arrAllad() As Variant: Let arrAllad() = Array(227, 209, 237 …………...................………………………………………………)
    Dim resMtch As Variant
     Let resMtch = Application.Match(Asc(MySingleCarrot), arrAllad(), 0)
        If IsError(resMtch) Then
        MsgBox prompt:="This character is not alllaowd  " & Asc(MySingleCarrot)
        Else ' build new string
        Dim NewString As String
         Let NewString = NewString & MySingleCarrot
        End If
    Next cnt
 Range("A2").Value = NewString
End Sub

_.___________________

More help I cannot give because:
_1) I do not have Arabic Excel
_2) I go now away for Xmas

If you still need help , then I come back in a few days,

Goodbye, Merry XMAS
:-)
See you later next week maybe

Alan

[YasserKhalil]

Mr. Alan
All the characters in range("a1") is Arabic letters and they are ok. Just the last character is the hidden
Can you tell me in simple words what I have to do to explain more?
Have a nice time

[LisaGreen]

What about chopping the last chr off if it's 254?

Lisa

[YasserKhalil]

Thanks a lot for reply
The problem Lisa is that I need to check for any hidden characters regardless it was 254 or not. I can get rid of it easily (that's not the point)

[LisaGreen]

Do you know the codes of the hidden characters please???

Lisa