• 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


  • 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


Well-Known Member
Then why not just use the code from post#2?
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 ..

Marc L

Excel Ninja
In fact it can work with your original attachment but not with your last one as it removes any non standard character and ? …​

Marc L

Excel Ninja
Well working with your initial attachment :​
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
                V(R, 1) = StrConv(B, vbUnicode)
           .Value2 = Application.Trim(V)
    End With
End Sub


Excel Ninja
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 ..
With following 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
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.


Well-Known Member
Thank you very much ..
I tested the code and I noticed a difference at results as for this part

It seems the result is different too ..

What's of the three output is used when dealing with the whole column ..?

Marc L

Excel Ninja
But this doesn't work for me at all (I tried in both samples)
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 …​


Well-Known Member
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
بسم الله الرحمن الرحيم


Excel Ninja
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.


Well-Known Member
Thanks a lot shrivallabha for this plenty of explanation. Thank you very much
As for the MatchType to be True as illustrated I tried
Sub findmethod()
    Range("B1:B20").Replace ChrW(1600), "", xlPart, , , True
End Sub
But seems not to fix the problem ..

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 ..?