Search and Replace in VBA Using Non English Characters

Hello, I am not sure where to post this question so trying here.

I have an Excel spreadsheet which I download from a website daily which contains "foreign" characters in some names. An example is "ň" which is Czech. The "foreign" character renders correctly in the spreadsheet but when I paste it into a macro which will perform a search and replace the "foreign" character appears as "?" instead of "ň". I have installed the Czech language plug-in for Excel but this has not solved my problem.

Would anyone know how to search for "ň" and replace with the letter "n"? Is there another plugin for VBA in Excel? The replaced character is used in a vlookup within the VBA macro so has to be changed in order for the vlookup to work as planned.

Other names containing the same accent "č", react with a "?" as well.

I hope this makes sense.

Thanks,

Dave

Originally Posted by thestampdad

...An example is "ň" which is Czech ...
... Would anyone know how to search for "ň" and replace with the letter "n"? ...

Have you tried it like this below ?

1. System settings
Start => Control Panel => Regional and Language options => Administrative tab => Language for non-Unicode programs => Change system locale => Czech

2. Editor vba
Tools => Options => Editor format => Font: => Some_Font (Central Europe)

Originally Posted by 6StringJazzer

There is no way to enter these characters directly into the VBA editor (that I have ever seen). You will have to write a line of code for each character you want to replace.

You have to determine the Unicode numeric code for each character and then replace with the desired substitute. Here is the code for ň

You can enter the characters into a worksheet and use the UNICODE function to find the numeric value.

Please Login or Register  to view this content.

Thank you, this worked well.

Dave

Thank you for the suggestion. I was leery of changing my settings in case there would be unwanted changes elsewhere so I did not attempt this change.

Dave

Thank you for the thorough code, unfortunately I could not get it to run. Not sure why. I put the code in the sheet I was working on, ran the code but it did not replace anything and no error messages. I am a beginner when it comes to VBA and most of my code is created by recording it. I did find that 6StringJazzer's example worked for my purposes.

Dave

Originally Posted by KOKOSEK

Try UDF like this:

Please Login or Register  to view this content.

Usage:

Formula:

Please Login or Register  to view this content.

or obviously:

Formula:

Please Login or Register  to view this content.

	C
9	Soucasná vláda je u moci proto, že se tak rozhodlo dostatecné množství volicu. Dostatecné množství volicu se tak rozhodlo proto, že melo jako základní cíl...
10	Soucasna vlada je u moci proto, ze se tak rozhodlo dostatecne mnozstvi volicu. Dostatecne mnozstvi volicu se tak rozhodlo proto, ze melo jako zakladni cil...

p.s. text in example is absolutely random from one of czech website.

Thank you for the thorough code, unfortunately I could not get it to run. Not sure why. I put the code in the sheet I was working on, ran the code but it did not replace anything and no error messages. I am a beginner when it comes to VBA and most of my code is created by recording it. I did find that 6StringJazzer's example worked for my purposes.

Dave

Thank you KOKOSEK, that worked very well. One small thing that it does not do is maintain the Case of the letter, how do I match lowercase or uppercase? The example I gave happened to be lower case but there are other characters such as "Ĉ" that need to remain in uppercase once the conversion is done.

Thank you again.

Dave

Thank you again KOKOSEK. Unfortunately, the file I download is a dynamic one and can change from day to day. It contains a list of Professional Hockey Players from the NHL and there are players from many countries who are listed with their countries characters in some cases. I don't have any new ones at the moment.

I did take some time and digested what you have in the 2 main lines of code and can see that the first line is the ChrW set and the second is the corresponding English alpha equivalent. I saw that you added for "Ĉ" (268, 67) which is appreciated. As I come across new combinations I will add it to the code you provided. I found a very extensive list online https://stackoverflow.com/questions/...code-character. Someone posted a macro here that creates all the possible conversions into an Excel spreadsheet. I found this very useful to see what your code is doing.

Thank you again very much for taking the time to help me with this.

Dave