Find and replace all Foreign Characters

Hi

I have workbooks I downloaded daily which can be up to 10,000 rows in data containing many foreign characters and accents.

I am trying to find a way to replace all of those characters and replace them with their standard counterpart, for example e´ becomes e.

Is there some code I can add into my existing macro (I use to manipulate the data) to clean the entire file of these "problem" characters ?

Help please.

Regards

Jon

Hi Leith

Good Point. I should have mentioned that.

It will mainly be European - French, Spanish, German in particular

Cheers

Jon

Hi Leith

What exactly do you mean that "The diacritical marks such as grave accent, acute accent, circumflex, and diaeresis, can be ignored" It is generally these that are causing us the most problems !

To fill you in the picture a little more.

The Royal Mail has a system that will not accept any "non standard" characters and so our postings to France in particular is causing a lot of problems.

We need to strip out those type of symbols.

With Regard to the Spanish - that would replace with N
With Regard to the German - that would replace with B

Many thanks

Jon

Hi Leith

What exactly do you mean that "The diacritical marks such as grave accent, acute accent, circumflex, and diaeresis, can be ignored" It is generally these that are causing us the most problems !

To fill you in the picture a little more.

The Royal Mail has a system that will not accept any "non standard" characters and so our postings to France in particular is causing a lot of problems.

We need to strip out those type of symbols.

With Regard to the Spanish - that would replace with N
With Regard to the German - that would replace with B

Many thanks

Jon

Hi Leith

Yes, that is just what I am trying to do.

Cheers

Jon

Hi Leith

I actually took some time to look into all of the problem characters we have experienced to date.

The list follows:-

Replace: à = a
Replace: À = A
Replace: â = a
Replace: Â = A
Replace: ä = a
Replace: Ä = A
Replace: æ = a e
Replace: Æ = A E
Replace: ç = c
Replace: Ç = C
Replace: é = e
Replace: É = E
Replace: è = e
Replace: È = E
Replace: ê = e
Replace: Ê = E
Replace: ë = e
Replace: Ë = E
Replace: î = i
Replace: Î = I
Replace: ï = i
Replace: Ï = I
Replace: ô = o
Replace: Ô = O
Replace: œ = o e
Replace: Π= O E
Replace: ù = u
Replace: Ù = U
Replace: û = u
Replace: Ù = U
Replace: ü = u
Replace: Ü= U

Cheers

Jon

Hi Leith

Many thanks for the work you have put into this.

I downloaded your sample file, but when I try and run the macro i get the following error message :-

Run Time Error '429'
ActiveX component can't create object

Not sure if it makes a difference, but I am working on an Imac running Excel for Mac 2004 V 11.5.4

I wonder if you can tell me what I am doing wrong ?

Cheers

Jon

Hi Leith

I got my shipping department to run it on their PC's and they have a different error :-

Run-time error '13' Type mismatch

They get this error when they run the macro on the following:

Vista and Office 2007 / Windows 7 and office 2010 and also on XP and office 2007

Cheers

Jon

Hi Leith

Did you manage to find anything out as to why we keep getting the error messages ?

Cheers

Jon

This should work without ActiveX

once you've imported your data, select all the cells you'd like to filter, and run the filterSelection sub.

(it may take a while to run due to the fact that it's using VBA to look at every character in your data. you could probably use some more logic to skip cells based on their number format too.)

Please Login or Register  to view this content.

Hi Gregor y

Many thanks for jumping in this thread. I tried your macro on the mac excel and it does not appear to work.

Is that likely due to the Mac platform - I presume it was working OK on your testing it in a windows platform ?

Cheers

Jon

Hi Leith

If it help you (or anyone else)

When you debug the run time error '13' ti highlights the following row :-

ReplaceCharacters Data(1, I)

Hope this casts some light on the problem

Cheers

Jon

Originally Posted by endoskeleton

Hi Gregor y

Many thanks for jumping in this thread. I tried your macro on the mac excel and it does not appear to work.

Is that likely due to the Mac platform - I presume it was working OK on your testing it in a windows platform ?

Cheers

Jon

It did work in windows excel, i'm not entirely familiar with what is and is not available on the mac version of excel, but you could start with removing all the application code (this was only included to increase spead)

or you could try

Please Login or Register  to view this content.

.
.
also note that if the code from the previous post was stopped in the middle of a run you'll typically want to turn the application stuff back on.

Please Login or Register  to view this content.

Hi Gregor y

Many thanks the original code worked great on the windows machines that the report runs on. thankyou.

There was one character that it was not picking up. this was an "A" with a "~" over the top (I don't know how to recreate it on a mac)

After doing some research I found it is Chr(195), but when I tried adding this to your code it simply removed the character not replaced it with an "A"

I simply added f(195) = "A":

What did I do wrong ?

Cheers

Jon

Depending on where you added it to that should have worked. The : is a way of putting multiple lines of code onto one line; however it is also used for line labels such as ResetApp:

To add in another substitution I'd add use the blank line between the f()... line and the dim i as long line.

Please Login or Register  to view this content.

.
originally i just used a text to columns on your post and some simple cell formulas found in cell J54 of the attached to copy and pasted into the VBA editor, to build the f()... line.

Hi All,

Does anyone knows the final and corrected code that does not show the error 13 on line ReplaceCharacters Data(1, I)?

Thanks,
Ionut

Guys,

Try this code.


Sub CleanUp()

' Clean up special characters within a range

Range("A1:Y12000").Select

' Define how the characters get replaced
Selection.Replace What:="‘", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="’", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="‚", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="“", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="”", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="„", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False

'The foreign characters are wired so we have to replace them. yuhuu


Selection.Replace What:="À", Replacement:="a", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="Á", Replacement:="a", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="Â", Replacement:="a", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="Ã", Replacement:="a", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="Ä", Replacement:="a", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="Å", Replacement:="a", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="Æ", Replacement:="a", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="Ç", Replacement:="c", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="È", Replacement:="e", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="É", Replacement:="e", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="Ê", Replacement:="e", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="Ë", Replacement:="e", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="Ì", Replacement:="i", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="Í", Replacement:="i", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="Î", Replacement:="i", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="Ï", Replacement:="i", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="Ð", Replacement:="d", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="Ñ", Replacement:="n", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="Ò", Replacement:="o", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="Ó", Replacement:="o", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="Ô", Replacement:="o", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="Õ", Replacement:="o", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="Ö", Replacement:="o", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="×", Replacement:="x", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="Ø", Replacement:="o", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="Ù", Replacement:="u", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="Ú", Replacement:="u", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="Û", Replacement:="u", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="Ü", Replacement:="u", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="Ý", Replacement:="y", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="Þ", Replacement:="p", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="ß", Replacement:="ss", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="à", Replacement:="a", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="á", Replacement:="a", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="â", Replacement:="a", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="ã", Replacement:="a", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="ä", Replacement:="a", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="å", Replacement:="a", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="ç", Replacement:="c", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="è", Replacement:="e", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="é", Replacement:="e", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="ê", Replacement:="e", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="ë", Replacement:="e", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="ì", Replacement:="i", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="í", Replacement:="i", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="î", Replacement:="i", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="ï", Replacement:="i", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="ð", Replacement:="o", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="ñ", Replacement:="n", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="ò", Replacement:="o", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="ó", Replacement:="o", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="ô", Replacement:="o", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="õ", Replacement:="o", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="ö", Replacement:="o", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="ø", Replacement:="o", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="ù", Replacement:="u", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="ú", Replacement:="u", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="û", Replacement:="u", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="ü", Replacement:="u", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="ý", Replacement:="y", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="þ", Replacement:="p", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="ÿ", Replacement:="y", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="æ", Replacement:="ae", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="@", Replacement:=" at ", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="&", Replacement:=" and ", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False

'The asterisk character is weird. If you don't remove it, it replaces everything in your range

Selection.Replace What:="+", Replacement:=" and ", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False

Selection.Replace What:="¢", Replacement:=" cents ", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="#", Replacement:=" number ", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="£", Replacement:=" pounds ", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="²", Replacement:=" squared", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="%", Replacement:=" percent ", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="°", Replacement:=" degrees ", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="™", Replacement:=" trademark ", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False


End Sub

Kr,
Ionut

This evokes a Microsoft Access Function dhTranslate but the translation list is extensive and may be helpful to anyone looking for a comprehensive list of foreign character translations:

'---> Translate specific list of foreign characters into 8-bit characters flattened:

sStringIn = dhTranslate(sStringIn, "ÀÁÂÃÄÅÆàáâãäåæçÇÈÉÊËèéêëÌÍÎÏìíîïÑñÒÓÔÕÖòóôõöØøÙÚÛÜùúûüÝýÿßÐ", "AAAAAAAaaaaaaacCEEEEeeeeIIIIiiiiNnOOOOOoooooOoUUUUuuuuYyysD")

I have found this thread useful. Only issue was the macro code did not work for me on MacOS version of Excel (16.22), here is mine:

Private Sub ReplaceCharacters(ByRef TextRef As Variant)

Dim Key As Variant
Dim RegExp As Object
Dim Text As String

Set RegExp = CreateObject("VBScript.RegExp")
RegExp.Global = True

LoadDictionary

Text = TextRef

For Each Key In Dict.Keys
RegExp.Pattern = Key
Text = RegExp.Replace(Text, Dict(Key))
Next Key

If TypeName(TextRef) = "Range" Then
TextRef.Value = Text
Else
TextRef = Text
End If

End Sub
Sub ReplaceForeignLetters()
'
' ReplaceForeignLetters Macro
'

'
Cells.Replace What:="à", Replacement:="a", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=True
Cells.Replace What:="â", Replacement:="a", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=True
Cells.Replace What:="ä", Replacement:="a", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=True
Cells.Replace What:="À", Replacement:="A", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=True
Cells.Replace What:="Â", Replacement:="A", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=True
Cells.Replace What:="Ä", Replacement:="A", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=True
Cells.Replace What:="æ", Replacement:="a e", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=True
Cells.Replace What:="Æ", Replacement:="A E", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=True
Cells.Replace What:="ç", Replacement:="c", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=True
Cells.Replace What:="Ç", Replacement:="C", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=True
Cells.Replace What:="é", Replacement:="e", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=True
Cells.Replace What:="è", Replacement:="e", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=True
Cells.Replace What:="ê", Replacement:="e", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=True
Cells.Replace What:="ë", Replacement:="e", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=True
Cells.Replace What:="É", Replacement:="E", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=True
Cells.Replace What:="È", Replacement:="E", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=True
Cells.Replace What:="Ê", Replacement:="E", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=True
Cells.Replace What:="Ë", Replacement:="E", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=True
Cells.Replace What:="î", Replacement:="i", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=True
Cells.Replace What:="ï", Replacement:="i", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=True
Cells.Replace What:="Î", Replacement:="I", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=True
Cells.Replace What:="Ï", Replacement:="I", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=True
Cells.Replace What:="ô", Replacement:="o", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=True
Cells.Replace What:="Ô", Replacement:="O", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=True
Cells.Replace What:="œ", Replacement:="o e", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=True
Cells.Replace What:="Œ", Replacement:="O E", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=True
Cells.Replace What:="ù", Replacement:="u", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=True
Cells.Replace What:="û", Replacement:="u", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=True
Cells.Replace What:="ü", Replacement:="u", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=True
Cells.Replace What:="Ù", Replacement:="U", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=True
Cells.Replace What:="Ù", Replacement:="U", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=True
Cells.Replace What:="Ü", Replacement:="U", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=True
End Sub

ß should be replaced by sz, not a B.

Hi Leith,

I had the same issue as Jon. This code is so helpful thank you!

I need to also include letters such as Ł, but its almost as if they don't exist in VBA. Do you know a way i can include these?

Thank you!

Jessica