Excel - Replace accented chars with regular chars

[xxarmoxx]

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

[westconn1]

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

[xxarmoxx]

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

[xxarmoxx]

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

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

[westconn1]

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

[xxarmoxx]

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

[westconn1]

just made a dedicated function for what you want
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
ActiveSheet.Cells(Row, col) = repaccent(ActiveSheet.Cells(Row, col))

[anhn]

Here you are. This function will work much better:

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

[xxarmoxx]

Thanks guys!