Solved

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

Posted on 2016-08-18
7
1,060 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
Technology Partners: 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

Technology Partners: 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!

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

749 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