How to remove all non alphanumeric characters from a string except period and space in excel?

Insert this function into a new module in the Visual Basic Editor:

Function AlphaNumericOnly(strSource As String) As String
    Dim i As Integer
    Dim strResult As String

    For i = 1 To Len(strSource)
        Select Case Asc(Mid(strSource, i, 1))
            Case 48 To 57, 65 To 90, 97 To 122: 'include 32 if you want to include space
                strResult = strResult & Mid(strSource, i, 1)
        End Select
    Next
    AlphaNumericOnly = strResult
End Function

Now you can use this as a User Define Function, i.e. if your data is in cell A1, place this formula in an empty cell =AlphaNumericOnly(A1).

If you want to convert a large range directly, i.e. replace all the non-alphanumeric characters without leaving the source, you can do this with another VBA routine:

Sub CleanAll()
    Dim rng As Range

    For Each rng In Sheets("Sheet1").Range("A1:K1500").Cells 'adjust sheetname and range accordingly
        rng.Value = AlphaNumericOnly(rng.Value)
    Next
End Sub

Simply place this sub in the same module and execute it. Be aware though, that this will replace any formulas in the range.

Here' an alternate method of removing "whatever characters you want" from a string using pattern matching.

• The example below removes everything except letters, numbers, spaces and periods ([A-Z.a-z 0-9])

• For improved efficiency it also utilizes VBA's seamless conversion between Strings and Byte Arrays:

cleanString Function:

Function cleanString(str As String) As String
    Dim ch, bytes() As Byte: bytes = str
    For Each ch In bytes
        If Chr(ch) Like "[A-Z.a-z 0-9]" Then cleanString = cleanString & Chr(ch)
    Next ch
End Function

---

More Information:

• For more about creating patterns for the Like operator, see:
  • VBA: Like Operator description
  • better info in the VB.NET: Like Operator description
• More about how Byte Arrays and Strings are basically interchangeable

I was looking for a more elegant solution than the one I came up with. I was going to use ashleedawg's code above as it certainly is neater than my code. Ironically, mine ran 30% quicker. If speed is important (say you have a few million to do), try this:

    Public Function AlphaNumeric(str As String) As String
    Dim i As Integer

    For i = 1 To Len(str)
        If InStr(1, "01234567890ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz. ", Mid(str, i, 1)) Then AlphaNumeric = AlphaNumeric & Mid(str, i, 1)
    Next
End Function

There's a surprise around every corner with VBA. I'd never imagine this would be quicker...