how do you strip accents of characters in a data set using vba but without using udf formulas

[tipclaydon]

Hi all

ive got a dataset in excel that has names and addresses and i would like to strip any accented letters and replace with normal letters as the data needs saving as a csv file.

i do have this code that allows it but requires the use of formulas which isnt really ideal for this situation. is there a way to do it so the script goes thru the data and just replaces the characters were needed?

Function StripAccent(thestring As String)
    Dim A As String * 1
    Dim B As String * 1
    Dim i As Integer
    
    Const AccChars= "ŠŽšžŸÀÁÂÃÄÅÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖÙÚÛÜÝàáâãäåçèéêëìíîïðñòóôõöùúûüýÿ"
    Const RegChars= "SZszYAAAAAACEEEEIIIIDNOOOOOUUUUYaaaaaaceeeeiiiidnooooouuuuyy"
    For i = 1 To Len(AccChars)
        A = Mid(AccChars, i, 1)
        B = Mid(RegChars, i, 1)
        thestring = Replace(thestring, A, B)
        thestring = worksheetfunction.substitute(thestring,"ß","ss")
    Next
    StripAccent = thestring
End Function

 

[Domenic]

The following code assumes that the sheet containing the data is the active sheet, and that Column A contains the data, starting at Row 2...

Option Explicit

Sub test()

    Dim lastRow As Long
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    Dim i As Long
    For i = 2 To lastRow
        Cells(i, "A").Value = StripAccent(Cells(i, "A").Value)
    Next
    
    MsgBox "Completed!", vbExclamation
    
End Sub

Function StripAccent(thestring As String)
    Dim A As String * 1
    Dim B As String * 1
    Dim i As Integer
    
    Const AccChars = "ŠŽšžŸÀÁÂÃÄÅÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖÙÚÛÜÝàáâãäåçèéêëìíîïðñòóôõöùúûüýÿ"
    Const RegChars = "SZszYAAAAAACEEEEIIIIDNOOOOOUUUUYaaaaaaceeeeiiiidnooooouuuuyy"
    For i = 1 To Len(AccChars)
        A = Mid(AccChars, i, 1)
        B = Mid(RegChars, i, 1)
        thestring = Replace(thestring, A, B)
        thestring = WorksheetFunction.Substitute(thestring, "ß", "ss")
    Next
    StripAccent = thestring
End Function

Hope this helps!

 

[DanteAmor]

Hi @tipclaydon.

Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.​

Try the following, it works for all texts in a column. Set the "A:A" column to the column you need, or even if you want multiple columns change it to "A:C" for example.

Sub replacecharacters()
  Dim i As Long, a As String, b As String
  
  Const AccChars = "ŠŽšžŸÀÁÂÃÄÅÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖÙÚÛÜÝàáâãäåçèéêëìíîïðñòóôõöùúûüýÿ"
  Const RegChars = "SZszYAAAAAACEEEEIIIIDNOOOOOUUUUYaaaaaaceeeeiiiidnooooouuuuyy"
  
  With Range("A:A")
    For i = 1 To Len(AccChars)
      a = Mid(AccChars, i, 1)
      b = Mid(RegChars, i, 1)
      .Replace a, b, xlPart, , True
    Next
    .Replace "ß", "ss", xlPart, , True
  End With
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------

 

[tipclaydon]

Hi @tipclaydon.

Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.​

Try the following, it works for all texts in a column. Set the "A:A" column to the column you need, or even if you want multiple columns change it to "A:C" for example.

Sub replacecharacters()
  Dim i As Long, a As String, b As String
 
  Const AccChars = "ŠŽšžŸÀÁÂÃÄÅÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖÙÚÛÜÝàáâãäåçèéêëìíîïðñòóôõöùúûüýÿ"
  Const RegChars = "SZszYAAAAAACEEEEIIIIDNOOOOOUUUUYaaaaaaceeeeiiiidnooooouuuuyy"
 
  With Range("A:A")
    For i = 1 To Len(AccChars)
      a = Mid(AccChars, i, 1)
      b = Mid(RegChars, i, 1)
      .Replace a, b, xlPart, , True
    Next
    .Replace "ß", "ss", xlPart, , True
  End With
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------

that seems to be working great thank you