Solved

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

Posted on 2016-08-18
7
769 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
  • 4
  • 3
7 Comments
 
LVL 18

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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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 18

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 18

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
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.

856 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