We help IT Professionals succeed at work.

how to use an excel formula to remove accent marks from characters, like ñ é ü í ó

I have a list of customers address. Some folks from Germany, Sweden, etc., have funny squiggles above or below many of the letters. I would like to make a simple formula to take the letter and then replace it with a regular ASCII letter that won't cause me any errors in my shipping application.

i.e. say column A has the original address with all the tildes, accents and squiggles. Column B will return the same complete address, but with the necessary letters replaced with the standard ASCII equivalent. Column B will be the "clean" list.

Can you please help?

:D
Comment
Watch Question

Marketing Consultant
Commented:
This macro works decent with Excel 2010 and it checks for lowercase and uppercase letters. You should be able to add whatever accented marks this one misses. Just follow the pattern in the macro; it's a one for one substitution. Marketers do get international data from 3rd party vendors, even when users register their products. It just happens. And if you can clean things up in Excel, it's a lot better than seeing ????s show up in web ad content too.
Sub ReplaceAccentChars()
  Dim X As Long, Diacritic As Variant, Normal As Variant
  Diacritic = Split(Trim(Replace(StrConv("ÀÁÂÃÄÅàáâãäåÈÉÊËèéêëÌÍÎÏìíîïÑñÒÓÔÕÖòóôõöÙÚÛÜùúûüÝýÿ", vbUnicode), Chr(0), " ")))
  Normal = Split(Trim(Replace(StrConv("AAAAAAaaaaaaEEEEeeeeIIIIiiiiNnOOOOOoooooUUUUuuuuYyy", vbUnicode), Chr(0), " ")))
  For X = 0 To UBound(Diacritic)
    ActiveSheet.UsedRange.Replace Diacritic(X), Normal(X), xlPart, MatchCase:=True
  Next
End Sub

Open in new window

Author

Commented:
Awesome @SerenaHsi

Thanks!

Next question, is there a way to do this with Google Docs Spreadsheets (on Google Drive)?

Author

Commented:
Next questions,
How exactly do I set this up in Excel 2010? Do I go into Visual Basic Editor? Please could you send a couple of screenshots?

Author

Commented:
Regarding Google Docs I think I figured this out myself, I'm going to use an add-in called "Power Tools"
Serena HsiMarketing Consultant

Commented:
For Google Sheets, goto:
Replace accent marks as a formula

For tutorial on how to mod and use Google scripts in Google Docs:
https://developers.google.com/apps-script/quickstart/macros
Serena HsiMarketing Consultant

Commented:
Microsoft Office Support KB on how to access the Developer toolbar.

Author

Commented:
Thanks! :D