Replace special character in whole column

[YasserKhalil]

Hello everyone

I am trying the 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

Columns(2).Replace What:=Chr(220), Replacement:=""

I also tried using ChrW but doesn't work too

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-pr...eplace-special-character-in-whole-column.html

 

[Fluff13]

How about

Sub Yasser()
    With Range("B1:B11")
        .Value = Evaluate("if({1},substitute(" & .Address & ",unichar(1600),""""))")
    End With
End Sub

 

[YasserKhalil]

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]

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.

Sub chk()
    Range("C1") = ChrW(1600)
    Debug.Print ChrW(1600)
End Sub

 

[YasserKhalil]

@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]

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 …​

​

 

[YasserKhalil]

@Marc L
How checking the length and got 4 ..? Just type this in a cell and use LEN and you will get just 1
ـ

 

[Marc L]

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]

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]

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​

 

[YasserKhalil]

Thanks a lot for this useful formula. So the length of the special characters here are 3

 

[Fluff13]

The length of the character is 1, but you have 3 of them in the cell.

 

[Fluff13]

Interestingly this seems to work

    Range("B:B").Replace " " & ChrW(1600), " "

 

[Marc L]

I tried the same with Sheet1.UsedRange.Columns(2) but does not work and that's ok with Range("B:B") … ​

​

 

[YasserKhalil]

In real there is no space before this character.. It is in between other characters

 

[Marc L]

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 !​

​

 

[Fluff13]

In real there is no space before this characIter.. It is in between other characters

In that case can you supply a sample file that demonstrates that?

 

[YasserKhalil]

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

Copy to different cells

 

[Marc L]

Test done like my post #16 code : that seems ok, some chars was deleted …​

​

 

[YasserKhalil]

I tried the code in Post #16 and none of the characters removed at all ..!!!

 

[Marc L]

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 …​

​

 

[YasserKhalil]

Here's another sample

 

[Marc L]

For your initial attachment as another starter :​

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]

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]

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) …​

​