?
Solved

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

Posted on 2016-08-18
7
Medium Priority
?
2,247 Views
Last Modified: 2016-08-18
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
0
Comment
Question by:gregholl
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 19

Accepted Solution

by:
Serena Hsi earned 2000 total points
ID: 41761456
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

1
 
LVL 1

Author Comment

by:gregholl
ID: 41761654
Awesome @SerenaHsi

Thanks!

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

Author Comment

by:gregholl
ID: 41761661
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?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:gregholl
ID: 41761666
Regarding Google Docs I think I figured this out myself, I'm going to use an add-in called "Power Tools"
0
 
LVL 19

Expert Comment

by:Serena Hsi
ID: 41761816
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
0
 
LVL 19

Expert Comment

by:Serena Hsi
ID: 41761817
Microsoft Office Support KB on how to access the Developer toolbar.
0
 
LVL 1

Author Closing Comment

by:gregholl
ID: 41761930
Thanks! :D
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question