remove non alphanumeric from a cell

[Flicker]

Hi,

I want to remove non alphanumeric character from a cell.

From To
A@@@@BC ABC
A B123**D AB123D

alphanumeric character that I want to keep are a-z A-Z and 0-9

As far as I check, substitute function limit to replace only 1 character at a time.

multiple substitute function limited up to 8 time.

The question are;

1. How to apply "OR" in SUBSTITUTE function?

2. are there any function "NOT IN" and kick those character out?

Thanks :D

 

[JamesW]

Hi,

You could try this UDF:

Function removeSpecial(sInput As String) As String
    Dim sSpecialChars As String
    Dim i As Long
    sSpecialChars = "\/:*?@""<>|"
    For i = 1 To Len(sSpecialChars)
        sInput = Replace$(sInput, Mid$(sSpecialChars, i, 1), "")
    Next
    removeSpecial = sInput
End Function

Add more special chars to the list as required.

 

[meldoc]

Hello

Try this.

Excel Workbook
	A	B
1	A@@@@BC	ABC
2	A B123**D	AB123D
Sheet1

Excel 2010

Cell Formulas
Range	Formula
B1	=Remove(A1)

Private RX As Object

Private Sub CreateRX()
    Set RX = CreateObject("VBScript.RegExp")
End Sub

Function Remove(ByVal s As String) As String
    If RX Is Nothing Then CreateRX
    With RX
        .Pattern = "[^0-9A-Za-z]"
        .Global = True
        Remove = .Replace(s, "")
    End With
End Function

 

[GTO]

Here's another UDF.

Function CleanText(ByVal Text As String) As String
Static REX As Object '<--- RegExp
    
    If REX Is Nothing Then Set REX = CreateObject("VBScript.RegExp")
    With REX
        .Global = True
        .Pattern = "[^a-zA-Z0-9]"
        CleanText = .Replace(Text, vbNullString)
    End With
End Function