RXReplace function - Function that removes spaces and special characters from a text string

Question

Years ago I had a database set up that used a function that I thought was called RXREPLACE. It removed all spaces and special characters from a text string.  Unfortuanately, the database is long gone and I'd like to recreate the function in case we need it in the future.

As always, thanks for the help!!

Ken K.

Answer 1

The following is a function I once wrote for multiple replacements:

Public Function MultiReplace(varInput, ParamArray varReplacements())

    ' call like this:

    ' MultiReplace("abcdefghijk","c","V","e","X","j","Y","m","Z")

    ' to return this:

    ' abVdXfghiYk

    Const MESSAGETEXT = "Uneven number of replacements parameters."

    Dim n As Integer

    Dim varOutput As Variant

    Dim intParamsCount As Integer

    If Not IsNull(varInput) Then

        intParamsCount = UBound(varReplacements) + 1

        If intParamsCount Mod 2 = 0 Then

            varOutput = varInput

            For n = 0 To UBound(varReplacements) Step 2

                varOutput = Replace(varOutput, varReplacements(n), varReplacements(n + 1))

            Next n

        Else

            MsgBox MESSAGETEXT, vbExclamation, "Invalid Operation"

        End If

    End If

    MultiReplace = varOutput

End Function

In your case you'd call it along these lines:

    ? MultiReplace("ab c#defg hijk"," ","","#","")

to replace spaces and the hash sign with zero length strings:

    abcdefghijk

or you might want to remove hashes and replace spaces with underscore characters for instance:

    ? MultiReplace("ab c#defg hijk"," ","_","#","")                         

    ab_cdefg_hijk

Answer 2

Hi,

you can use a sequence of replace function as:

Str=Replace(Str,"!","")

Str=Replace(Str,"?","")

Str=Replace(Str,"#","")...

Mimmo