Function to Remove Unwanted Characters from String in MS Access

Function to Remove Unwanted Characters in String

The following custom function can be used in Microsoft Access to remove or strip out unwanted characters from a string.

Function RemoveUnwantedCharacters(Input_String)
'**********************************************************************
'
'   Only Keep Specific Characters in String (Remove all Others)
'
'**********************************************************************

    '   Declare Variables
    Dim characters_to_keep As Variant
    Dim string_character As String
    Dim i As Long

    '   Set the initial output to blank
    RemoveUnwantedCharacters = ""

    '   Define list of characters to keep
    characters_to_keep = "0123456789ABCDEFGHIJKLMNOPQRSTUVWYZ"

    '   Loop through characters in string
    For i = 1 To Len(Input_String)

        '   Select character i from string
        string_character = Mid(Input_String, i, 1)

        '   Keep character if it is one of the characters to keep
        If InStr(characters_to_keep, string_character) Then
            RemoveUnwantedCharacters = RemoveUnwantedCharacters & string_character
        End If

    Next

End Function

In the above function, we have defined a list of characters we want to keep. In this example, we wish to keep numbers 0 through 9 and letters A through Z.

All other characters will be removed from the string passed into the function.

The list of characters to keep can easily be customized to your liking. For example, if you wanted to remove all non-numeric characters, you would revise the list of characters to keep as follows.

'   Define list of characters to keep (only numeric characters)
characters_to_keep = "0123456789"

How to Use this Custom Function?

To use this custom function, you’ll want to add it to a module in your Microsoft Access database. To do this, navigate to the Create tab and then select Module as shown below.

Create new MS Access module

When the Visual Basic for Applications Editor opens, copy and paste the code into the module.

Add custom function to MS Access module

Now you can use this custom function in your database project in many different ways, such as within your VBA code or even within a query. The following example shows how you might use it in a query.

Use function in MS Access Query

The final image below shows the output when used in a query. The function is used to strip out any characters that are not numeric or alphabetic characters.

Query results