replace accented characters with regular characters in Excel

[baskar5353]

hi,

i'm having record with Spanish word in excel i want to replace into regular character.

whole sheets i want to replace into regular characters where the accent character is there

how to do in VBA

 

[Fishboy]

hi,

i'm having record with Spanish word in excel i want to replace into regular character.

whole sheets i want to replace into regular characters where the accent character is there

how to do in VBA

Do you have a list of the accented characters you want to be replaced and what you want them replaced with?

 

[baskar5353]

"á" change as "a" like wise from "a to z" i want to change them as regular character.

 

[Fishboy]

"á" change as "a" like wise from "a to z" i want to change them as regular character.

I'm afraid I don't know of any clever or quick ways to do this (I'm sure someone here will though). The only way I can think of doing it is very long an convoluted involving specific lines of VBA for each accented character to be replaced. If you look at my example below it will replace any á with a, any é with e and any ó with o. You can copy the format of my code for each other type of accented character but I appreciate this is probably not the most efficient way of doing it. I have made the different sections of the code different colours so you can easily see how small each section to be replicated is. Simply change the Replace What and the Replacement ​to suit your needs.

Sub Replace_Accents()
Dim ws As Worksheet
For Each ws In Worksheets
    With ws
        .Cells.Replace What:="á", Replacement:="a", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    End With
Next ws
For Each ws In Worksheets
    With ws
        .Cells.Replace What:="é", Replacement:="e", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    End With
Next ws
For Each ws In Worksheets
    With ws
        .Cells.Replace What:="ó", Replacement:="o", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    End With
Next ws

End Sub

 

[FormR]

Hi, another possible option (probably haven't covered all of the different chars, but you can add as required).

Sub m()
Const sFm As String = "ÀÁÂÃÄÅàáâãäåÈÉÊËèéêëÌÍÎÏìíîïÑñÒÓÔÕÖòóôõöÙÚÛÜùúûüÝýÿ" 'add more as required
Const sTo As String = "AAAAAAaaaaaaEEEEeeeeIIIIiiiiNnOOOOOoooooUUUUuuuuYyy" 'add more as required
Dim i As Long, ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    For i = 1 To Len(sFm)
        ws.Cells.Replace Mid(sFm, i, 1), Mid(sTo, i, 1), LookAt:=xlPart, MatchCase:=True
    Next i
Next ws
End Sub