How to Remove Accents in Excel

When dealing with large amounts of data in a spreadsheet, it may be necessary to remove accents from Excel to avoid incompatibilities with specific functions. The good news is that there is a simple method to get around this problem.

How to Remove Accents in Excel

although the Microsoft Excel does not have a native function to remove accents, it is possible to create a custom function, known as UDF (User Defined Function), to accomplish this task. Creating a UDF allows users to customize Excel to their specific needs.

For the accent problem, you can create a UFD to match the base character of the search cell with that of the cells in the search range and remove the accents from both. Check out:

How to create a UFD

1. Open Excel;
2. Pressure as teclas alt + F11 to open the VBA editor;
3. Right-click on the “Project – VBA Project” window;
4. Go to “Insert” and click on “Module”;
5. Insert the following code into the right frame:

   Function Acento(Caract As String)
   Dim A As String
   Dim B As String
   Dim i As Integer
   Const AccChars = “ŠŽšžŸÂÂÂÃÄÅÇÈÉËìÎÏÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂ‎‎‎‎‎‎‎‎‎‎‎‎‎‎‎‎‎‎‎‎‎)‎”s)””
   Const RegChars = “SZszYAAAAAACEEEEIIIIDNOOOOOUUUUYaaaaaaceeeeiiiidnooooouuuuyy”
   For i = 1 To Len(AccChars)
   A = Mid(AccChars, i, 1)
   B = Mid(RegChars, i, 1)
   Caract = Replace(Caract, A, B)
   Next
   Accent = Character
   End Function
   

How to remove accent

Once you create the function in Excel, you can use it just like any other function to remove accent from worksheet cells. For example, if you have cell A1 accented, just select another empty cell and enter the following formula:

Then the selected cell will be filled with the unaccented word. This is a great solution for those who don’t want to lose the original information and work with the spreadsheet without accents.

Now, if you want to improve your skills with features, formulas and functions, here are 15 tips for using Excel.