Quickly Replace Accented Characters with Regular Characters in Excel

Have you imported customer names, product lists, or reports into Excel and noticed accented characters such as é, ü, ñ, ç, ô? In many cases, these characters can cause problems when sorting, filtering, searching, matching data, or exporting information to other systems.

• José → Jose
• München → Munchen
• François → Francois
• García → Garcia

Many databases, websites, and business systems prefer plain English characters. Therefore, replacing accented characters with regular characters is a common data-cleaning task in Excel. Unfortunately, Excel does not provide a built-in feature to remove accented characters directly. In this tutorial, we will introduce several effective methods to help you quickly convert accented characters into regular characters in Excel.

• Replace accented characters manually with Find and Replace
• Replace accented characters with user-defined function
• Batch replace accented characters quickly with Kutools for Excel

Replace accented characters manually with Find and Replace

If your worksheet only contains a few accented characters, Excel's built-in Find and Replace feature can help you manually replace them. However, this method becomes tedious when your data contains many different accented characters because you must replace each one individually.

Steps to replace accented characters manually

1. Select the range where you want to replace accented characters.
2. Press Ctrl + H to open the Find and Replace dialog box.
3. In the Find what box, enter an accented character, such as: é.
4. In the Replace with box, enter the regular character: e.
5. Click Replace All.

   

6. A dialog box will appear showing how many replacements were completed successfully. Click OK.

   

7. Repeat the process for other accented characters such as:
   • à → a
   • ü → u
   • ñ → n
   • ç → c

Limitations of this method

While simple, this approach has several drawbacks:

• You must replace characters one by one.
• It is very time-consuming for large datasets.

If you frequently work with imported international data, a more automated method is recommended.

Replace accented characters with user-defined function

For advanced Excel users, VBA can automate the replacement process and handle multiple accented characters at once. This method is useful when you regularly clean imported data and are comfortable using macros.

Steps

1. Press Alt + F11 to open the Microsoft Visual Basic for Applications window.
2. Click Insert > Module.
3. Paste the following VBA code into the module window:

   Function StripAccent(thestring As String)
   Dim A As String * 1
   Dim B As String * 1
   Dim i As Integer
   
   Const AccChars = "ŠŽšžŸÀÁÂÃÄÅÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖÙÚÛÜÝàáâãäåçèéêëìíîïðñòóôõöùúûüýÿ"
   Const RegChars = "SZszYAAAAAACEEEEIIIIDNOOOOOUUUUYaaaaaaceeeeiiiidnooooouuuuyy"
   
   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

   

4. Press Alt + Q to close the VBA editor and return to the worksheet.
5. Enter the following formula into a blank cell (here A2 is the cell containing the accented characters):

   =StripAccent(A2)

6. Press Enter, then drag the fill handle down to apply the formula to other cells.

   

Batch replace accented characters quickly with Kutools for Excel

Find and Replace can only replace one accented character at a time, while VBA requires manually editing code to support more characters, which is not user-friendly for most users. If you want a faster and easier solution, Kutools for Excel's Replace Any Characters feature can quickly replace multiple accented characters at once with built-in rules.

Steps

1. Select the range where you want to replace accented characters.
2. Select Kutools > Text > Replace Any Characters.

   

3. In the Replace Any Characters dialog box:
   1. Select the built-in Accented characters scenario.
   2. By default, all preset accented character rules are selected.
      Tip: The built-in rules already cover most commonly used accented characters, so in most cases, no additional setup is needed. You can keep only the rules you need, or manually add new rules for accented characters that are not included in the preset list.
   3. Preview the replacement results in real time, then click OK.

      

Result

Now all accented characters in the selected range are instantly converted into regular characters.

Advantages of Kutools for Excel

Compared with manual replacement, or VBA, Kutools offers several major advantages:

• No formulas or VBA required
• Built-in accented character rules
• Batch replace multiple character types simultaneously
• Real-time preview before replacement
• Supports custom replacement rules
• Much faster and easier for large datasets

In addition, the upgraded Replace Any Characters feature is more flexible than traditional replace tools because it allows you to manage reusable rule scenarios for different text-cleaning tasks.

Conclusion

Replacing accented characters with regular characters helps standardize text data and avoid issues when searching, matching, sorting, or exporting data in Excel. In this article, we introduced three different methods, from manual replacement to VBA automation and the faster Kutools for Excel solution. Among them, Kutools for Excel offers the simplest and most efficient way to batch replace accented characters with built-in rules and just a few clicks.