How To Replace Accented Characters With Regular Latin Characters

How To Replace Accented Characters With Regular latin Characters ?

We got a request from a client, to convert the accented characters of multiple Excel files to plain Latin. Excel VBA is great and really handy but there is no method to perform such a task using VB code. On the other hand C# inherits all beautiful .Net libraries, and it’s so easy to solve the above problem. Why not bring the .Net world close to the “vintage” world of VBA?

So here is what we did. We prepared a method in C# that fixes the problem and then made it public to the VBA IDE:

				
					            string accentedStr = "Staré Město";
            byte[] tempBytes;
            tempBytes = Encoding.GetEncoding("ISO-8859-8").GetBytes(accentedStr);
            string asciiStr = Encoding.UTF8.GetString(tempBytes);
				
			

And from the Excel VBA IDE:
We registered the C# Library from Tools | References, and we added the following method:

				
					Public Sub Convert()
                    
    Application.Cursor = xlWait
    
    'Create Object from C#
    Dim obj As CSharpTools.CSharpTools
    Set obj = New CSharpTools.CSharpTools
        
    Dim CountRows As Long
    Dim ActiveCol
    ActiveCol = ActiveCell.Column
    Cells(1, ActiveCol).Select
    
    'Get the last row in the current region
    CountRows = Cells(1, ActiveCol).End(xlDown).Row
    
    Dim i As Long
    For i = 1 To CountRows
    
        'Convert each cell to Latin characters
        Cells(i, ActiveCol).Value = obj.ConvertFromUTF8ToLatin(Cells(i, ActiveCol).Value)
    
    Next i
    
    Application.Cursor = xlDefault
    
End Sub
				
			

With the above code we can convert a whole column at no time at all !

If you need assistance on converting your documents please use our Contacts Form, we would love to help ! 

 

latest posts

How To Replace Accented Characters With Regular latin Characters ?

We got a request from a client, to convert the accented characters of multiple Excel files to plain Latin. Excel VBA is great and really handy but there is no...

Hafnium Hack – How to run the patches, with simple detailed instructions.

Why make such a “How to”? Because all articles we found so far, talk about what to do, but none shows how to do it.So let’s start. 1. HOW TO KNOW IF YOU...

How can AI be used in web scraping?

Hi! Just in case, we have developed a web scraping application. I work for Synergy USA llc and you can find more about it, googling "Synergy Custom web scraping" latest...

What is “Bonjour Service” on my computer? Is it a virus?

It’s a service made by Apple. It’s used to help your computer connect to devices like TimeMachine, Apple TV etc.It can also run in Windows.Do not worry, it’s not a...