Excel - Replace accented chars with regular chars

Whats a good way for me to go through all the columns in a spread sheet and replace accented characters with non-accented characters?

you can do a loop for each cell in usedrange
then use replace to replace the characters you wish to replace,
there could be other solutions, but that is probably the simplest
you can nest replace to replace mutiple characters, but i am not sure how deep, so you might have to use multiple replace on each cell, depending how many accented characters you might need to replace

How can I use the replace function within the code? please post an example.

I tried this but its not working, it cannot find the sub or function 'Replace'

Code:

---

Dim col As Integer
col = TextBox1.Text

  For Row = 2 To ActiveWorkbook.ActiveSheet.UsedRange.Rows.Count
 
Dim theinput As String
theinput = ActiveSheet.Cells(Row, col)

Dim out As String

out = Replace(theinput, "A", "AAA")

ActiveSheet.Cells(Row, col) = out

Next Row

---

what version of excel?
replace does not exist in '97, but is in 2000 on

Im on Excel 2004 for Macs. It should work the same as 2000 vba wise.

just made a dedicated function for what you want

vb Code:

Function repaccent(inputstr As String)
mystr = ""     'put here the list of accented characters
myrepstr = ""    'put here the list of characters to replace them with in the same order (position is critical)
' both strings must be the same length
For i = 1 To Len(inputstr)
    pos = InStr(mystr, Mid(inputstr, i, 1))  'check if each character is in the accented list
    If Not pos = 0 Then inputstr = Left(inputstr, i - 1) & Mid(myrepstr, pos, 1) & Mid(inputstr, i + 1)
    ' if it is replace it in the string with the equivalent character from the second string
Next
repaccent = inputstr
End Function

call it like

vb Code:

ActiveSheet.Cells(Row, col) = repaccent(ActiveSheet.Cells(Row, col))

Here you are. This function will work much better:

VB Code:

Option Explicit
 
'-- Add more chars to these 2 string as you want
'-- You may have problem with unicode chars that has code > 255
'-- such as some Vietnamese characters that are outside of ASCII code (0-255)
Const AccChars = "ŠŽšžŸÀÁÂÃÄÅÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖÙÚÛÜÝàáâãäåçèéêëìíîïðñòóôõöùúûüýÿ"
Const RegChars = "SZszYAAAAAACEEEEIIIIDNOOOOOUUUUYaaaaaaceeeeiiiidnooooouuuuyy"
 
Sub StripAccent(aRange As Range)
'-- Usage: StripAccent Sheet1.Range("A1:C20")
   Dim A As String * 1
   Dim B As String * 1
   Dim i As Integer
   
   For i = 1 To Len(AccChars)
      A = Mid(AccChars, i, 1)
      B = Mid(RegChars, i, 1)
      aRange.Replace What:=A, _
                     Replacement:=B, _
                     LookAt:=xlPart, _
                     MatchCase:=True
   Next
 
End Sub

Thanks guys!