• 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

YasserKhalil

Well-Known Member
Hello everyone

I am trying the code

Code:
Sub Test()
    Application.Calculation = xlManual
        Columns(2).Replace What:="ـ", Replacement:=""
    Application.Calculation = xlAutomatic
End Sub
This doesn't work for whole column although when looping through each cell and using Replace it works with each cell
Is there a way to make that works on the whole column.. I tried putting the character ~ before the character but doesn't work too

I even tried usign Chr(220) but doesn't work too

Code:
Columns(2).Replace What:=Chr(220), Replacement:=""
I also tried using ChrW but doesn't work too

Code:
Columns(2).Replace What:=ChrW(1600), Replacement:="", LookAt:=xlPart
Posted at this link too
https://www.eileenslounge.com/viewto...p?f=30&t=33708
https://www.excelforum.com/excel-programming-vba-macros/1298155-replace-special-character-in-whole-column.html
 

Attachments

YasserKhalil

Well-Known Member
Thanks a lot Fluff for this perfect solution

I am wondering why using ChrW(1600) doesn't work .. although this is the same Unicode character!!
 

Fluff13

Active Member
No idea why it doesn't work, but VBA doesn't seem to like that character.
If you run this you will see that the C1 value & immediate window value are different.
Code:
Sub chk()
    Range("C1") = ChrW(1600)
    Debug.Print ChrW(1600)
End Sub
 

YasserKhalil

Well-Known Member
@Fluff13
As for me I got the character two times (the same) as I have installed Arabic and this is special Arabic character
It is produced when typing Arabic and I press Shift Key and J letter ...
 

Marc L

Excel Ninja
Hi !​
In fact some characters are coded on two bytes (so two characters) and this is the case here​
as you can see if you keep only the wanted char on a cell and you check the length : 4 instead of 2 …​
As Unichar function does not exist in my old Excel version I'm trying to find out a workaround …​
 

Marc L

Excel Ninja
From your attachment I delete in a cell all characters except the specific one to replace​
then in the next cell I use the worksheet function LEN and the result is 4 …​
 

YasserKhalil

Well-Known Member
Yes you're right as the character in each cell is typed four times.. And not the length of the character itself equals to 4
 

Fluff13

Active Member
As for me I got the character two times (the same) as I have installed Arabic and this is special Arabic character
I get a small dot in the cell & a ? in the immediate window, which means VBA struggles to interpret that character.

If I put the cursor after the t of "Lot" & I have to press the right arrow 5 times to get to the F of "For"
Also if I put this formula in C1 & copy across
=UNICODE(MID($B2,COLUMN(A1),1))

I get

84​
104​
97​
110​
107​
115​
32​
97​
32​
108​
111​
116​
32​
1600
1600
1600
32​
70​
111​
114​
32​
72​
101​
 

Marc L

Excel Ninja
It's how chars are coded with 'mixed West & East' …​
In fact as column B is the first column used in your worksheet used range :​
Sheet1.UsedRange.Columns(1).Replace " " & ChrW(1600) & " ", " ", xlPart
my bad, the reason why Fluff13 was faster !​
 

YasserKhalil

Well-Known Member
you can try this sentence
بـــــســـــم الله الــــرحـــمــــن الــــرحــــيـــــم

Copy to different cells
 

Marc L

Excel Ninja
Try removing a space before or after the #1600 and see … Try as well Fluff13 way in post #13.​
If not the expected result, attach a workbook with raw data in column B and expected result in column E for example …​
 

Marc L

Excel Ninja
For your initial attachment as another starter :​
Code:
Sub Demo1()
    Dim V, R&
    With Range("B1", [B1].End(xlDown))
        V = .Value2
        For R = 1 To UBound(V):  V(R, 1) = Replace(V(R, 1), ChrW(1600), ""):  Next
       .Value2 = Application.Trim(V)
    End With
End Sub
 

YasserKhalil

Well-Known Member
Thanks a lot Mr. Marc
In fact my target is not to use loops at all .. I can solve the issue in several ways but my need is no LOOP
Thank you very much
 

Marc L

Excel Ninja
As Demo1 well works with your last attachment and as it's the easier way …​
For the moment my test computer does not have an Excel version where the worksheet function UNICODE exists​
so Fluff13 is the better placed to help you (the reason why far better is a thread than a private conversation) …​
 
Top