Excel Remove Accents

ExcelRemoveAccents.vb

Function StripAccent(thestring As String)
Dim A As String * 1
Dim B As String * 1
Dim i As Integer
Const AccChars= "áäčďéěíĺľňóôőöŕšťúůűüýřžÁÄČĎÉĚÍĹĽŇÓÔŐÖŔŠŤÚŮŰÜÝŘŽ"
Const RegChars= "aacdeeillnoooorstuuuuyrzAACDEEILLNOOOORSTUUUUYRZ"
For i = 1 To Len(AccChars)
A = Mid(AccChars, i, 1)
B = Mid(RegChars, i, 1)
thestring = Replace(thestring, A, B)
Next
StripAccent = thestring
End Function

How to setup the script in Excel

1. press Alt + F11 keys in Excel, and it opens the Microsoft Visual Basic for Applications window
2. Click Insert > Module, and paste the following macro in the Module Window.
3. Then go to a blank cell and paste the formula in a cell: =StripAccent(theString), for example =StripAccent("ù"), or =StripAccent(A2).
4. Save Excel file as .xlsm (Excel file with macros support)

Thank you very much, it works just fine! You just forgot the ë and the Ë. Both of those accents are very used in France (ex: noël = christmas)

Thanks! You've saved my day!

You can also add it as a macro to be applied to any selection with:

Sub RemoveDiacritics()
    Dim cell As Object
    
    If Not ActiveSheet.UsedRange Is Nothing Then
        Application.EnableEvents = False
    
        For Each cell In Selection
            If Not IsEmpty(cell) Then
                cell = StripAccent(CStr(cell))
            End If
        Next
        
        Application.EnableEvents = True
    End If
End Sub

I have an updated script to account for some missing accents. Thank you for sharing this script.

Function StripAccent(thestring As String)
Dim A As String * 1
Dim B As String * 1
Dim i As Integer
Const AccChars = "ãáäcçdëèéeíïllñnòóôøoörštúuuüýržÃÁÄÇCDÈËÉEÏÍLLÑNØÒÓÔOÖRŠTÚUUÜÝRŽ"
Const RegChars = "aaaccdeeeeiillnnoooooorstuuuuyrzAAACCDEEEEIILLNNOOOOOORSTUUUUYRZ"
For i = 1 To Len(AccChars)
A = Mid(AccChars, i, 1)
B = Mid(RegChars, i, 1)
thestring = Replace(thestring, A, B)
Next
StripAccent = thestring
End Function

Has anybody an idea how I can include these characters to be replaced: "ąśł"

Has anybody an idea how I can include these characters to be replaced: "ąśł"

You can add the new accent character to the string AccChars, then add the corresponding 'regular' (non-accented) character to RegChars, in the same position.

Hi

I have a weird behavior, the script returns something, namely the same accented text as before. The characters to be replaced are all in the AccChars/RegChars strings. Any idea what is causing this behavior?

Chris

Actually what I wrote was wrong. The characters are not in the string, they just look similar. When I try to insert the characters I want replaced, Excel puts a questionmark instead of the character. Does this mean the code page I am using for the macro does not support these characters? I can display them on the Excel sheet.