• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Replace special character in whole column

Fluff13 said:
Then why not just use the code from post#2?
Click to expand...
I really liked it and considered it the first and the most perfect solution.
My target now is to know why replace failed with the whole column (why with this character specially)??
You have told there may be a workaround as for that ..
 
In fact it can work with your original attachment but not with your last one as it removes any non standard character and ? …​
​
 
Well working with your initial attachment :​
Code: 
Sub Demo2()
        Dim V, R&, B() As Byte, C&
    With Range("B1", [B1].End(xlDown))
            V = .Value2
        For R = 1 To UBound(V)
                B = StrConv(V(R, 1), vbFromUnicode)
            For C = 0 To UBound(B)
                If B(C) = 63 Then B(C) = 32
            Next
                V(R, 1) = StrConv(B, vbUnicode)
        Next
           .Value2 = Application.Trim(V)
    End With
End Sub
 
YasserKhalil said:
I really liked it and considered it the first and the most perfect solution.
My target now is to know why replace failed with the whole column (why with this character specially)??
You have told there may be a workaround as for that ..
Click to expand...
With following code
Code: 
    Dim i As Long
    For i = 1 To Len(Selection.Value)
        Debug.Print i & ")." & Asc(Mid(Selection.Value, i, 1)) & "-" & AscB(Mid(Selection.Value, i, 1)) & "-" & AscW(Mid(Selection.Value, i, 1))
    Next i

The output in immediate window is
Code: 
1).84-84-84
2).104-104-104
3).97-97-97
4).110-110-110
5).107-107-107
6).115-115-115
7).32-32-32
8).97-97-97
9).32-32-32
10).108-108-108
11).111-111-111
12).116-116-116
13).32-32-32
14).63-64-1600
15).63-64-1600
16).63-64-1600
17).32-32-32
18).70-70-70
19).111-111-111
20).114-114-114
21).32-32-32
22).72-72-72
23).101-101-101
24).108-108-108
25).112-112-112

which matches with what Fluff has posted earlier. It looks as if by default VBA uses the ASCII charset. So if it has to deal with characters as the one in your example it gets confused and therefore gives unexpected results.
 
Thank you very much ..
I tested the code and I noticed a difference at results as for this part
220-64-1600

It seems the result is different too ..

What's of the three output is used when dealing with the whole column ..?
 
YasserKhalil said:
But this doesn't work for me at all (I tried in both samples)
Click to expand...
As it well works on my side with post #1 attachment, the reason why you have differences with shrivallabha (and me),​
maybe different Regional Settings or different language installed on Excel side …​
(Test my code with 220 rather than 63, by the way what is the char #220 on your side ? On my side it's a small black square : ▄ )​
​
So give a try to the Range.Replace method with its parameter MatchByte set to True …​
​
 
Thanks a lot. I have tested your code again and it works but the result is not correct as I got each letter separated ... And in Arabic there are a lot of letters that are connected together
What I got looks like that
ب س م الله ال رح م ن ال رح ي م
and it should look like that
بسم الله الرحمن الرحيم
 
After bit of searching around, what I understood is:

- Excel uses UTF-8 (UTF = Unicode Transformation Format) encoding. Simple way to find this is to save Excel file as .zip and then navigating to "yourexcelfilename.zip\xl\worksheets" and opening the xml sheet which will show the encoding used. Encoding converts the character set to binary format.

- Unicode is character set. Most of the other language characters such as Arabic are thus supported through Unicode.

- On our computers when the system tries to look up the '-' like character and is unable to resolve then it responds by stating Chr(63) which is "?" on our computers. This is standard UTF-8 behavior to return Chr(63) for "unable to resolve" category characters. Incidentally, if we assume that it is Chr(63) which of course it is not and try to replace then it acts as wildcard and therefore results in a blank cell. On your computer it is returning 220 which is U with umlaut on my system.

- Even replacement with Unicode character perhaps doesn't replace the string stored in memory appropriately and therefore you again experience unexpected results which roughly happens like this
1. -->{Visible String==Data in Binary Format}
2. -->Replace Command{Unicode Char==Binary Format}
3. -->Replacement Occurs in Binary Format Data
4. -->{Resultant Data in Binary Format==Returned Result}
I am not sure about exactly how steps 2 and 3 get performed internally on each system but this is where I think the "unreliable" output is coming in.

So these differences are going to show different results unless there's a stable method which reads and interprets the character in the same manner irrespective of the locale. It will be wild goose chase otherwise IMHO.
 
Thanks a lot shrivallabha for this plenty of explanation. Thank you very much
As for the MatchType to be True as illustrated I tried
Code: 
Sub findmethod()
    Range("B1:B20").Replace ChrW(1600), "", xlPart, , , True
End Sub
But seems not to fix the problem ..

Code: 
If b(c) = 220 Then b(c) = 32
In this line I changed 63 to 220 .. What about the 32 which is the space .. Is there an alternative that is more suitable to my case ..?
 
You must log in or register to reply here.
Back
Top