Microsoft Office Forums - View Single Post

Souriane · 05-06-2022, 07:45 AM

Hi!

Macropod gave me this macro 10 years ago. Still using it!

It changes all accented letters to non-accented letters. But I can't figure out how to modify it in order that spaces be replaced by underscore?

Code:

Function CleanString(StrInput As String) As String
'Enlève les caractères accentués d'une chaîne
'Auteur : Macropod 2011-04-01

    Dim StrOutput As String, StrChr As String, i As Integer
    For i = 1 To Len(StrInput)
        StrChr = Mid$(StrInput, i, 1)
        Select Case AscW(StrChr)
        Case 192 To 197: StrChr = "A"
        Case 198: StrChr = "AE"
        Case 199: StrChr = "C"
        Case 200 To 203: StrChr = "E"
        Case 204 To 207: StrChr = "I"
        Case 208: StrChr = "D"
        Case 209: StrChr = "N"
        Case 210 To 214, 216: StrChr = "O"
        Case 215: StrChr = "x"
        Case 217 To 220: StrChr = "U"
        Case 221: StrChr = "Y"
        Case 222, 254: StrChr = "p"
        Case 223: StrChr = "B"
        Case 224 To 229: StrChr = "a"
        Case 230: StrChr = "ae"
        Case 231: StrChr = "c"
        Case 232 To 235: StrChr = "e"
        Case 236 To 239: StrChr = "i"
        Case 240, 242 To 246, 248: StrChr = "o"
        Case 241: StrChr = "n"
        Case 249 To 252: StrChr = "u"
        Case 253, 255: StrChr = "y"
        End Select
        If StrChr Like "[A-Za-z0-9_]" Then
            StrOutput = StrOutput & StrChr
        End If
    Next
    CleanString = StrOutput
End Function

Thank you!

Souriane