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

I need to remove all non alphanumeric characters from a string except period and space in Excel. A solution using VBA rather than pure excel functions be just fine.

Solution 1:

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.

Solution 2:

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