Replace special character in whole column

Hello everyone

I am trying the code

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

I also tried using ChrW but doesn't work too

Please Login or Register  to view this content.

Posted at this link too
https://www.eileenslounge.com/viewto...p?f=30&t=33708
https://chandoo.org/forum/threads/re...-column.43145/

Don't know. This worked for me.

Please Login or Register  to view this content.

Thanks a lot. I tried but didn't work for me.

Originally Posted by Marc L

Hi Yasser,

check the ASCII code (the wide one) …

More details please Mr. Marc

Here's sample file

Thank you very much Mr. Pete
I recognize using SUBSTITUTE and I have tested that before .. Also used Replace for each cell in the range ( this approach worked fine)
What is not working is the replace for the whole range and I don't know why doesn't work although it is working for any other characters..

Do you mean to repeat the same code more than once ..?
if so I already did that but this doesn't let me get the desired output
The only and most solution that works very fine till now is this code (Fluff's code on Chandoo)

Please Login or Register  to view this content.

Which used the same logic as yours ..

@Pete
That's not hyphen at all. This is a special Arabic character that is produced when typing Arabic
I press the Shift Key then type the letter J (this produces this character)

Thanks a lot. It seems no way of escaping the loop.
The only way is to use Evaluate approach

** In the loop it is just sufficient and more flexible to use

Please Login or Register  to view this content.

Thanks a lot again but this doesn't work for me
In real file there are no spaces before this character .. It seems like that
بـــــســـــم الله الــــرحـــمــــن الــــرحــــيـــــم

The character is in between the other characters without spaces
The desired result is
بسم الله الرحمن الرحيم

@YasserKhalil - You posted using Chr(220), but Chr(95) is the ASCII subscript symbol

Please Login or Register  to view this content.

Thanks a lot everybody for sharing
@Jindon
Thank you very much. I tested the code and nothing changed as for me. The character is represented using ChrW(1600), how about 8204?

Please Login or Register  to view this content.

Thanks a lot Ben for sharing
The same ideas discussed and all are working ..

The target of the thread is to know why this character specially doesn't work when replacing in the whole column like any other character .. and is there any workaround to do that using the same technique of using the code in the same way as Jindon offered ..

The 'Why" is simple enough. The symbol in the code is not the same as the symbol on the worksheet. Have you tried copying the symbol on the sheet into the 'Replace' statement in the code to see if it will then execute the replacement? There are multiple unicode values for look alike symbols of the subscript. Without knowing from where the symbol originated and what code the originator was using, it could be any of several code values that created the symbol.

The character is produced when typing Arabic. I press Shift + J letter >> this is how the character generated ..
I tried to replace the character manually and this doesn't work too

!!!!!!!!!!!!!!!!!!!!!!Deleted!!!!!!!!!!!!!!!!!!!!

@porucha vevrku - I wonder if the anomally you describe in post #28 might be caused by exclusion of the relevant code in the underlying software that drives VBA, Excel and even the compiler. Maybe I am reaching too far for some kind of logic that would explain this phenomenon, but there has to be something that can explain how the item can be created but not destroyed.

Originally Posted by Fluff13

It can be removed in Excel (see post#11), but not with VBA.
So it seems that xl & vba interpret that symbol differently for some reason

It is the zeros and ones being in different locations, but why? Bill Gates strikes again!!!

Originally Posted by YasserKhalil

... It seems like that
بـــــســـــم الله الــــرحـــمــــن الــــرحــــيـــــم
...
The desired result is
بسم الله الرحمن الرحيم

Maybe "it" ( ChrW(1600) ) to change/remove somehow this way ?

Although it is not "all at once", but cell by cell only, but we can keep trying further.

'B1' = بـــــســـــم الله الــــرحـــمــــن الــــرحــــيـــــم

Please Login or Register  to view this content.

Originally Posted by JLGWhiz

... might be caused by exclusion of the relevant code in the underlying software that drives VBA, Excel and even the compiler ...

Maybe, I don't know ...
Below is a recorded screenshot with the code running and a text file with messages from 'Debug.Print'.
A screen recording made with a some small program, gave a large result file, so I had to pack it into three zip files.
I added the '.zip' extension to the name of each zip file to be able to upload the file to the forum, so, it must be removed before unpacking.

Edit:

I replaced the zip archive with a new one

Originally Posted by porucha vevrku

Are you sure that this symbol/symbols ( _ ) are redundant/excess symbols, not created as a result of "stretching" the text/letters ?

Yes you are right. The symbols are stretched three times

Please Login or Register  to view this content.

This is the symbol
And I will attach a file with the symbol just in one cell A1

As for the files in post #35, the archive is corrupt
You can upload the file to external link

Thank you very much for great help in this issue. I have learned a lot from you