Remove Certain Characters from a String using UDF

Question

I have a column which contain cells that have some list of alphanumeric number system as follows:

4A(4,5,6,7,8,9); 4B(4,5,7,8); 3A(1,2,3); 3B(1,2,3), 3C(1,2)

On a cell next to it, I use a UDF function to get rid of special characters "(),;" in order to leave the data as

4A456789 4B4578 3A123 3B123 3C12

Function RemoveSpecial(Str As String) As String
    Dim SpecialChars As String
    Dim i As Long
    SpecialChars = "(),;-abcdefghijklmnopqrstuvwxyz"
    For i = 1 To Len(SpecialChars)
        Str = Replace$(Str, Mid$(SpecialChars, i, 1), "")
    Next
    RemoveSpecial = Str
End Function

For the most part this works well. However, on certain occasions, the cell would contain an unorthodox pattern such as when a space is included between the 4A and the parenthesized items:

4A (4,5,6,7,8,9);

or when a text appears inside the parenthesis (including two spaces on each side):

4A (4,5, skip 8,9);

or a space appears between the first two characters:

4 A(4,5,6)

How would you fix this so that the random spaces are removed except to delaminate the actual combination of data?

user_445425 · Answer

One strategy would be to substitute the patterns you want to keep before eliminating the "special" characters, then restore the desired patterns.

From your sample data, it look like you want to keep a space only if it follow ); or ),

Something like this:

Function RemoveSpecial(Data As Variant) As Variant
    Dim SpecialChars As String
    Dim KeepStr As Variant, PlaceHolder As Variant, ReplaceStr As Variant
    Dim i As Long
    Dim DataStr As String
    
    SpecialChars = " (),;-abcdefghijklmnopqrstuvwxyz"
    KeepStr = Array("); ", "), ")
    PlaceHolder = Array("~0~", "~1~") ' choose a PlaceHolder that won't appear in the data
    ReplaceStr = Array(" ", " ")
    DataStr = Data
    For i = LBound(KeepStr) To UBound(KeepStr)
        DataStr = Replace$(DataStr, KeepStr(i), PlaceHolder(i))
    Next
    For i = 1 To Len(SpecialChars)
        DataStr = Replace$(DataStr, Mid$(SpecialChars, i, 1), vbNullString)
    Next
    For i = LBound(KeepStr) To UBound(KeepStr)
        DataStr = Replace$(DataStr, PlaceHolder(i), ReplaceStr(i))
    Next
    RemoveSpecial = Application.Trim(DataStr)
End Function

Another strategy would be regular expressions (RegEx)

user_9758194 · Answer

It looks like a regular expression could come in handy here, for example:

Function RemoveSpecial(Str As String) As String
    
    With CreateObject("vbscript.regexp")
        .Global = True
        .Pattern = "\)[;,]( )|[^A-Z\d]+"
        RemoveSpecial = .Replace(Str, "$1")
    End With

End Function

I have used the regular expression:

\)[;,]( )|[^A-Z\d]+

You can see an online demo to see the result in your browser. The way this works is to apply a form of what some would call "The best regex trick ever!"

\)[;,]( ) - Escape a closing paranthesis, then match either a comma or semicolon before we capture a space character in our 1st capture group.
| - Or use the following alternation:
[^A-Z\d]+ - Any 1+ char any other than in given character class.

EDIT:

In case you have values like 4A; or 4A, you can use:

(?:([A-Z])|\))[;,]( )|[^A-Z\d]+

And replace with $1$2. See an online demo.

Remove Certain Characters from a String using UDF

2 Answers