Solved

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

Posted on 2016-08-18
7
1,651 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 500 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

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 will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

695 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