Excel Macro to Find & Replace Diacritic Characters

[excel8442]

I am trying the following but getting an error (on the row with --> ActiveDocument.Range.Find.Execute) in Excel 2011 for Mac. Any ideas?

Sub Diacritics_Replacement()


Dim strFind As String
Dim strReplace As String
Dim i As Long
strFind = "áéíóú"
strReplace = "aeiou"
For i = 1 To Len(strFind)
ActiveDocument.Range.Find.Execute FindText:=Mid$(strFind, i, 1), _
ReplaceWith:=Mid$(strReplace, i, 1), Replace:=wdReplaceAll
Next i


End Sub

 

[Rick Rothstein]

Give this macro a try...

Sub ReplaceDiacriticCharacters()
  Dim X As Long, Diacritic As Variant, Normal As Variant
  Diacritic = Split("á é í ó ú")
  Normal = Split("a e i o u")
  For X = 0 To UBound(Diacritic)
    ActiveSheet.UsedRange.Replace Diacritic(X), Normal(X), xlPart
  Next
End Sub

Note: This code does exactly what you requested meaning it only replaces the lower case diacritical characters you listed, not all of the possible diacritical characters there are.

 

[RickXL]

Hi and welcome to the MrExcel Message Board.

You seem to be using a Word macro in Excel. I think that may be the problem.

If you really want this to work in Excel then the macro recorded will give you some pointers.

Regards,

 

[excel8442]

@RickXL -- Good catch Thanks.

 

[excel8442]

Many thanks for this macro - I will try it.

Any idea where I can get a list of the possible diacritical characters for European languages (or at least the most common diacriticals and most common languages)?

I think I would need to run this macro for about the Top 30 diacriticals to catch most/all of them in my spreadsheet -- but not sure where to get those diacriticals from.

 

[RickXL]

I do not use a Mac so I cannot be sure. Windows has a Character Map program to help. I think Macs have character viewers as well.

You could try in Excel. Enter:
=CHAR(ROW())
in cell A1 and drag it down. It should work for at least the first 255 rows.

 

[excel8442]

That Excel formula looks quite promising. Might do the trick. Thanks.

I do not use a Mac so I cannot be sure. Windows has a Character Map program to help. I think Macs have character viewers as well.

You could try in Excel. Enter:
=CHAR(ROW())
in cell A1 and drag it down. It should work for at least the first 255 rows.

 

[Akuini]

Many thanks for this macro - I will try it.

Any idea where I can get a list of the possible diacritical characters for European languages (or at least the most common diacriticals and most common languages)?

I think I would need to run this macro for about the Top 30 diacriticals to catch most/all of them in my spreadsheet -- but not sure where to get those diacriticals from.

Maybe this would help:
http://www.mrexcel.com/forum/excel-...nted-characters-regular-characters-excel.html

 

[Rick Rothstein]

Maybe this would help:
http://www.mrexcel.com/forum/excel-...nted-characters-regular-characters-excel.html

Here is my macro with the characters from the last message at the link you posted...

[table="width: 500"]
[tr]
	[td]Sub ReplaceDiacriticCharacters()
  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[/td]
[/tr]
[/table]

 

[excel8442]

Very helpful -- many thanks!