How to delete everything but "normal" characters

[hellothere4]

Hello, I'm just looking for a simple way to delete all characters that aren't of the standard type, like a-z, 0-9, .'!? THANKS!!

 

[shg]

See Help for the CLEAN() function?

 

[Sektor]

Function ClearString(ByVal sWord As String) As String

    Dim i As Integer
    Dim sNewWord As String, sCharacter As String, sPattern As String
    Dim re As RegExp, m As Match
    
    sWord = "BCDa%"
    sPattern = "[a-z0-9.'!?]"

    Set re = New RegExp
    With re
        .IgnoreCase = True
        .Pattern = sPattern
    End With
    
    For i = 1 To Len(sWord)
        sCharacter = Mid$(sWord, i, 1)
        If re.Test(sCharacter) Then
            sNewWord = sNewWord & sCharacter
        End If
    Next
    
    ClearString = sNewWord
    
End Sub

 

[Sektor]

This code requires library. Go here: Tools -> Reference -> Microsoft VBScript Regular Expressions 5.5.

 

[Sektor]

Please, delete line: sWord = "BCDa%"

 

[pgc01]

Hi

If Clean() does not solve your problem then I think a udf approach like Sektor suggests will.

I think that in this case you don't need to both loop through the characters and use Regex's.

2 options.

1 - Use a RegEx, for ex.:

Function ClearString1(ByVal sWord As String) As String
 
    With CreateObject("VBScript.RegExp")
        .Pattern = "[^a-zA-Z0-9.'!?]"
        ClearString1 = .Replace(sWord, "")
    End With
End Function

2 - loop through the characters:

Function ClearString2(ByVal sWord As String) As String
    Dim i As Integer
    Dim sNewWord As String, sCharacter As String, sPattern As String
    
    sPattern = "[a-zA-Z0-9.'!?]"
    For i = 1 To Len(sWord)
        sCharacter = Mid$(sWord, i, 1)
        If sCharacter Like sPattern Then
            sNewWord = sNewWord & sCharacter
        End If
    Next
    
    ClearString2 = sNewWord
    
End Function

 

[brettdj]

I've used a different approach to the regex to remove all the non-confomring caharacters in one shot

If you add this VBA function to your workbook then you can then call it either as a
a) worksheet function
for example
=RegexRemove(A1)
in cell B1 to remove any non a-z0-9!.' from A1
b) via other code

hth

Dave

Function RegexRemove(strIn As String)
    Dim Regex
    Set Regex = CreateObject("vbscript.regexp")
    With Regex
        .IgnoreCase = True
        .Global = True
        .Pattern = "[^\w+\s\.\!']"
        RegexRemove = .Replace(strIn, vbNullString)
    End With
End Function

 

[brettdj]

dang .. same as pgc01's code which appeared in the time for me to complete my post except that I added a space to the ignore list (/s) and used case ignore and /w in place of a-zA-Z0-9

Cheers

Dave

 

[pgc01]

Hi

In my previous post I forgot to set the Global property of the RegEx:

Function ClearString1(ByVal sWord As String) As String
 
    With CreateObject("VBScript.RegExp")
        .Pattern = "[^a-zA-Z0-9.'!?]"
[COLOR=black]        .Global = True
[/COLOR]        ClearString1 = .Replace(sWord, "")
    End With
End Function

 

[pgc01]

...I added a space to the ignore list (/s) and used case ignore and /w in place of a-zA-Z0-9 ...

Hi Dave

Remark: You also added the + sign and the underscore to the ignore list.