replacing accented characters with non accented equivalents

[Paulos]

Having diligently uploaded a large file of records to an email database I notice that accented characters are causing lots of problems.

Has anyone some kind of automated method of changing "López" with an accented "o" into "Lopez" without.

i want to do this for several spanish characters.

Paul

 

[stanleydgromjr]

Paulos,

I have used the below Function to replace characters for e-mail addresses.

Press and hold down the 'ALT' key, and press the 'F11' key.

Insert a Module in your VBAProject, Microsoft Excel Objects

Copy the below code, and paste it into the Module1.

Private Function CheckStringCHAR(InString) As String

' CheckStringCHAR(InString)
' Returns its passed agrument, but with exchanged European? characters
' Function created 7/08/2003 by Stanley D. Grom, Jr.
'
CheckStringCHAR = ""
StringLength = Len(InString)

For i = 1 To StringLength
    
    SearchCHAR = Mid(InString, i, 1)
    
    Select Case SearchCHAR
        Case "Š"                ' 138
            FoundCHAR = "S"
        Case "Ž"                ' 142
            FoundCHAR = "Z"
        Case "š"                ' 154
            FoundCHAR = "s"
        Case "ž"                ' 158
            FoundCHAR = "z"
        Case "Ÿ"                ' 159
            FoundCHAR = "Y"
        Case "À"                ' 192
            FoundCHAR = "A"
        Case "Á"                ' 193
            FoundCHAR = "A"
        Case "Â"                ' 194
            FoundCHAR = "A"
        Case "Ã"                ' 195
            FoundCHAR = "A"
        Case "Ä"                ' 196
            FoundCHAR = "A"
        Case "Å"                ' 197
            FoundCHAR = "A"
        Case "Ç"                ' 199
            FoundCHAR = "C"
        Case "È"                ' 200
            FoundCHAR = "E"
        Case "É"                ' 201
            FoundCHAR = "E"
        Case "Ê"                ' 202
            FoundCHAR = "E"
        Case "Ë"                ' 203
            FoundCHAR = "E"
        Case "Ì"                ' 204
            FoundCHAR = "I"
        Case "Í"                ' 205
            FoundCHAR = "I"
        Case "Î"                ' 206
            FoundCHAR = "I"
        Case "Ï"                ' 207
            FoundCHAR = "I"
        Case "Ñ"                ' 209
            FoundCHAR = "N"
        Case "Ò"                ' 210
            FoundCHAR = "O"
        Case "Ó"                ' 211
            FoundCHAR = "O"
        Case "Ô"                ' 212
            FoundCHAR = "O"
        Case "Õ"                ' 213
            FoundCHAR = "O"
        Case "Ö"                ' 214
            FoundCHAR = "O"
        Case "Ù"                ' 217
            FoundCHAR = "U"
        Case "Ú"                ' 218
            FoundCHAR = "U"
        Case "Û"                ' 219
            FoundCHAR = "U"
        Case "Ü"                ' 220
            FoundCHAR = "U"
        Case "Ý"                ' 221
            FoundCHAR = "Y"
        Case "à"                ' 224
            FoundCHAR = "a"
        Case "á"                ' 225
            FoundCHAR = "a"
        Case "â"                ' 226
            FoundCHAR = "a"
        Case "ã"                ' 227
            FoundCHAR = "a"
        Case "ä"                ' 228
            FoundCHAR = "a"
        Case "å"                ' 229
            FoundCHAR = "a"
        Case "ç"                ' 231
            FoundCHAR = "c"
        Case "è"                ' 232
            FoundCHAR = "e"
        Case "é"                ' 233
            FoundCHAR = "e"
        Case "ê"                ' 234
            FoundCHAR = "e"
        Case "ë"                ' 235
            FoundCHAR = "e"
        Case "ì"                ' 236
            FoundCHAR = "i"
        Case "í"                ' 237
            FoundCHAR = ""
        Case "î"                ' 238
            FoundCHAR = "i"
        Case "ï"                ' 239
            FoundCHAR = "i"
        Case "ð"                ' 240
            FoundCHAR = "o"
        Case "ñ"                ' 241
            FoundCHAR = "n"
        Case "ò"                ' 242
            FoundCHAR = "o"
        Case "ó"                ' 243
            FoundCHAR = "o"
        Case "ô"                ' 244
            FoundCHAR = "o"
        Case "õ"                ' 245
            FoundCHAR = "o"
        Case "ö"                ' 246
            FoundCHAR = "o"
        Case "ù"                ' 249
            FoundCHAR = "u"
        Case "ú"                ' 250
            FoundCHAR = "u"
        Case "û"                ' 251
            FoundCHAR = "u"
        Case "ü"                ' 252
            FoundCHAR = "u"
        Case "ý"                ' 253
            FoundCHAR = "y"
        Case "ÿ"                ' 255
            FoundCHAR = "y"
        Case Else
            FoundCHAR = SearchCHAR
    End Select
    
    CheckStringCHAR = CheckStringCHAR & FoundCHAR
    
Next i
    
End Function

To use the function in your workbook you will need to go to a cell in a black column and copy the next formula in a cell. The "InString" in the formula should be replaced with the (same row):
=CheckStringCHAR(InString)

If your e-mail address is in cell A2, and the next available blank column is D. Then enter into cell D2 the following formula:
=CheckStringCHAR(A2)

All the European characters will be replaced by US characters.

Have a great day,
Stan

 

[posty]

:sehorn:

 

[Armando Montes]

This is what I use:

Sub acentos()
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

 

[posty]

This is what I use:

Sub acentos()
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

Not very solid with Excel, so how do you run this inside a spreadsheet?

 

[MANNG99]

Or you could simply use the Find and replace function?

"CTRL" and "f"

Then copy the accented letter and paste it into the "find what" field and type the unaccented letter in the replace box

Then click replace all

 

[posty]

Or you could simply use the Find and replace function?

"CTRL" and "f"

Then copy the accented letter and paste it into the "find what" field and type the unaccented letter in the replace box

Then click replace all

When there are a lot of special characters, this is time consuming IMO...

 

[nolme]

Quite an old topic but well referenced on Google.
Here's what I used :
https://www.youtube.com/watch?v=UXnwu5cLD8I