Find and Replace Macro to replace list of special characters

Hello,

Any VBA experts that could help with a Macro that can replace special/foreign characters with English equivalent across a whole worksheet?

Basically, our internal system corrupts when we try and upload the characters, rather than doing find and replace several times it would be good to do it at a click!

Below is what I need to find and replace. If someone can help with the "start" and "end" of the Macro and one example (if the below format is incorrect), I can populate the rest.
Thanks in advance.

fnd = "ø"
rplc = "o"

fnd = "ã"
rplc = "a"

fnd = "Š"
rplc = "S"

fnd = "Ž"
rplc = "Z"

fnd = "š"
rplc = "s"

fnd = "ž"
rplc = "z"

fnd = "Ÿ"
rplc = "Y"

fnd = "À"
rplc = "A"

fnd = "Á"
rplc = "A"

fnd = "Â"
rplc = "A"

fnd = "Ã"
rplc = "A"

fnd = "Ä"
rplc = "A"

fnd = "Å"
rplc = "A"

fnd = "Ç"
rplc = "C"

fnd = "È"
rplc = "E"

fnd = "É"
rplc = "E"

fnd = "Ê"
rplc = "E"

fnd = "Ë"
rplc = "E"

fnd = "Ë"
rplc = "E"

fnd = "Ì"
rplc = "I"

fnd = "Í"
rplc = "I"

fnd = "Î"
rplc = "I"

fnd = "Ï"
rplc = "I"

fnd = "Ð"
rplc = "D"

fnd = "Ñ"
rplc = "N"

fnd = "Ò"
rplc = "O"

fnd = "Ó"
rplc = "O"

fnd = "Ô"
rplc = "O"

fnd = "Õ"
rplc = "O"

fnd = "Ö"
rplc = "O"

fnd = "Ù"
rplc = "U"

fnd = "Ú"
rplc = "U"

fnd = "Û"
rplc = "U"

fnd = "Ü"
rplc = "U"

fnd = "Ý"
rplc = "Y"

fnd = "à"
rplc = "a"

fnd = "á"
rplc = "a"

fnd = "â"
rplc = "a"

fnd = "ã"
rplc = "a"

fnd = "ä"
rplc = "a"

fnd = "å"
rplc = "a"

fnd = "ç"
rplc = "c"

fnd = "è"
rplc = "e"

fnd = "é"
rplc = "e"

fnd = "ê"
rplc = "e"

fnd = "ë"
rplc = "e"

fnd = "ì"
rplc = "i"

fnd = "í"
rplc = "i"

fnd = "î"
rplc = "i"

fnd = "ï"
rplc = "i"

fnd = "ð"
rplc = "o"

fnd = "ñ"
rplc = "n"

fnd = "ò"
rplc = "o"

fnd = "ó"
rplc = "o"

fnd = "ô"
rplc = "o"

fnd = "õ"
rplc = "o"

fnd = "ö"
rplc = "o"

fnd = "ù"
rplc = "u"

fnd = "ú"
rplc = "u"

fnd = "û"
rplc = "u"

fnd = "ü"
rplc = "u"

fnd = "ý"
rplc = "y"

fnd = "ÿ"
rplc = "y"

Hello,

Thanks for replying, see attached (this is a small snippet of the data)

So for example, I would normally go to "Find and Replace" and replace ø with o then replace all. Then I would so the same with the a in Sao Paolo and so on. When I have hundreds of addresses I have to go through x amount of characters in find and replace. Ideally, I would like to have a macro that finds any offending characters and replaces them with the correct "English" letter (according to that list).

I know there will be more to the code, as in, find ø, and replace all with o, if none found, move onto the next one and repeat the process.

Is the below correct for one character and can I simply copy/paste from selection.find to add additional characters in?

Sub SimpleReplace()

Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "ø"
.Replacement.Text = "o"
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
End Sub

A M A Z I N G ! !
That seems to have hit the spot! Well done, ever so thankful.

If I need to add any in a live example would be ę (that I appear to have missed) can add that anywhere? e.g.

Dim v As Variant, i As Long
v = Array("ę", "ø", "o", "Š", "S"

Originally Posted by dcowiesmith

If I need to add any in a live example would be ę (that I appear to have missed)

Here is another macro that you can try which I have also set up to replace that ę character with an e...

Please Login or Register  to view this content.