[2003] Removing Accents

  • Thread starter Thread starter Bob Flaminio
  • Start date Start date
B

Bob Flaminio

In a sheet that I'm working on I need to replace any accented characters
with non-accented ones. For example, if a cell contained "déjà vu", the
output would need to be "deja vu".

The best I've come up with so far is about a zillion nested substitute()
functions, but there must be an easier way...
 
Hi Bob,

Here's a popular one used in the french NG.
I have written a more general one called MultiSubstitute(), that replaces any
chars of string1 that are present in string2 by their correspondent
(positionwise) chars of string3.
Look for MultiSubstitute in Google Groups.


' Usage:
' Your string in A1
' =Virer_Accents(A1) in B1
'Author is Alain Vallon, MVP.
'
Function Virer_Accents$(Chaine$)
Dim i As Integer, j As Integer
Dim x As String

j = Len(Chaine$)
For i = 1 To j
x = Asc(mid(Chaine$, i, 1))
Select Case x
Case 192 To 197: x = "A"
Case 200 To 203: x = "E"
Case 204 To 207: x = "I"
Case 209: x = "N"
Case 210 To 214: x = "O"
Case 217 To 220: x = "U"
Case 221: x = "Y"
Case 224 To 229: x = "a"
Case 232 To 235: x = "e"
Case 236 To 239: x = "i"
Case 241: x = "n"
Case 240, 242 To 246: x = "o"
Case 249 To 252: x = "u"
Case 253, 255: x = "y"
Case Else: x = Chr(x)
End Select
Virer_Accents = Virer_Accents & x
Next
End Function 'AV

Regards,

Daniel M.
 
Back
Top