Removing non-alpha characters from filename

[clhare]

Is there a fairly easy way to remove all non-alpha characters from the active document's filename with the exception of the underscore and resave the file?

For example, I need the macro to go through the filename and change filenames like "LETTER_O'NEAL" to "LETTER_ONEAL", and "LETTER_SMITH, JR." to "LETTER_SMITHJR", and "LETTER_DE LA ROSA" to "LETTER_DELAROSA", etc.

[macropod]

Hi Cheryl,

Stripping out the unwanted characters is fairly easy. You could use a function like:
Function CleanString(StrInput As String)
Const BadChrs As String = ".,' """
Dim i As Integer
For i = 1 To Len(BadChrs)
StrInput = Replace(StrInput, Mid(BadChrs, i, 1), vbNullString)
Next
CleanString = StrInput
End Function
which you could call from a sub as:
CleanString "input string"

For example:
Sub Test()
MsgBox CleanString(InputBox("What is the input string?"))
End Sub

[Jay Freedman]

The BadChrs string would have to include all the possible non-alphanumeric characters that could possibly appear in the inputs. That list could get large and unwieldy. Instead, I'd propose running through the input and keeping only the desired characters, like this:

Function CleanString2(StrInput As String) As String
Dim StrOutput As String
Dim charTest As String
Dim i As Integer

For i = 1 To Len(StrInput)
charTest = Mid$(StrInput, i, 1)
If charTest Like "[A-Za-z0-9_]" Then
StrOutput = StrOutput & charTest
End If
Next

CleanString2 = StrOutput
End Function

[Souriane]

Anyway to add to this code to change all accented letters to non-accented letters?

Like é, è, ê to e
à to a
ç to c
etc.

Thank you!

:friends:

Souriane

[macropod]

Hi Souriane,

That certainly complicates things. You could use a function like:
Function CleanString(StrInput As String) As String
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