Cannot find and delete a specific character

[chengafni]

Hello,
I am using a macro to remove certain characters from text stored in an Excel sheet. I discovered that there is a specific character, U+0640 Unicode (the Arabic Tatweel) that cannot be deleted.
Moreover, if I try to find the character using Excel's Find and replace something strange happens: if the sheet contains this character Excel "finds" it in all cells except those that actually contain it (if the sheet doesn't contain the character Excel reports no results).
Any idea what's going on?

Chen

[Keebellah]

No idea, have you copied the text to Notepad or another editor? Try it in Word and see what the character really looks like.
You can also have Excel display the actual ascii code

[chengafni]

I can see the character (it looks like an underscore) and Excel return its correct Unicode value. Still, finding and replacing doesn't work on it.

[Keebellah]

Have you tried character by character?

[chengafni]

what do you mean?

[Keebellah]

VBA macro, take it character by character
Using the mid(textstring,x,1)

[chengafni]

Thanks for the suggestion. However, even if it works in principle, I am working on a large amount of data and I cannot afford to go cell-by-cell character-by-character. I am using the Range.Replace method, which works fine with almost all characters.

[jolivanes]

Copy a sentence which has this special character in it into cell A1 and run this.
Does that tell you anything?

Please Login or Register  to view this content.

Re: and I cannot afford to go cell-by-cell character-by-character
Maybe you have to live with your problem

[xladept]

Try this:

Please Login or Register  to view this content.

[shg]

This worked for me:

Please Login or Register  to view this content.

[Keebellah]

Do you have a sample file to attach?

[chengafni]

See a sample file. It contains a list of unicode characters which I would like to delete and their respective values.
The file also contains a code to delete these characters. Notice that only some of them are deleted by the code. Any idea why?

[Keebellah]

Strange indeed, looks like extended ascii charterset.
I added three columsn with the three wingdings font and then you see the corresponding symbols but the search and replace will probably not work.
I do not have a solution for you, will keep looking and see if I find something

[chengafni]

You are right, changing the font type doesn't affect the results.
But at least I got one person to admit that there IS something strange going on here
Thanks for trying!

[Keebellah]

The reason can also be that maybe, like my system, you do not have the special characters instaalled, I ran into this some time ago whne I helped someone using Turkish fonts, the characters were not recognized until I implemented these.
I'm not at my own system now but you could take a look at international settings in your Windows and especially something about extended or special characters fonts

[xlnitwit]

Hi,

Have you tried using an array

Please Login or Register  to view this content.

[chengafni]

I just did, and it worked!
It is many times slower than Range.Replace but I guess I can combine the methods.
Many thanks!