Excel Macro to remove bad characters (special characters) in a string of text in a column

Try this.  Change this statement to the number of rows you have
For i = 2 to 100, if you have 1000 rows, make it For i = 2 to 1000.

Sub removeChars()
 
    Dim myArray As Variant
    Dim i As Integer
    Dim temp As String
    Dim tempOriginal As String
    
    myArray = Array("(", ",", "-", ";", "!", "", ":", ")", "'", "\n", "/n", "_", "[", "]") '0 to 13
        
    For i = 2 To 100 'Number of rows
        temp = Cells(i, 3).Value
        For p = 0 To 13 'Number of values in Array
            tempOriginal = Replace(temp, myArray(p), "")
            temp = tempOriginal
        Next
        Cells(i, 3).Value = temp
    Next
 
End Sub

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:

Hello superoneio,

My solution seems broadly teh same as Patrick, on a quick test I couldn't get PAtricks to work so I thought I would submit this ... if I am in error then please ignore this post in favour of PAtricks.  

Regards,

chris_bottomley

Function CRBReplace(strTest As String, strFind As String, Optional strReplacement As String = "", Optional bolGlobalReplace As Boolean = True, Optional bolMatchCase As Boolean = True)
Dim regEx As Object
    
    If Left(strFind, 1) <> "[" Then strFind = "[" & strFind
    If Right(strFind, 1) <> "]" Then strFind = strFind & "]"
    Set regEx = CreateObject("vbscript.regexp")
    With regEx
        .Pattern = strFind
        .Global = bolGlobalReplace
        .IgnoreCase = Not bolMatchCase
    End With
    
    CRBReplace = regEx.replace(strTest, strReplacement)
        
End Function

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:

superoneio,

I should have said the call would be for example:

=crbreplace(C2, ":,()bde", "")
placed in for example D2 and copied down should exclude the defined symbols from C into D

chris_bottomley

superoneio,

Correctionh ... SIgh!, I should have said the call would be for example:

=crbreplace(C2, ":,()", "")
placed in for example D2 and copied down should exclude the defined symbols from C into D

chris

superoneio,

In my example, I used the pattern string:

^ +| +$|\\n|/n|[^a-z0-9 ]

In RegExp, the | character acts as a logical or operator.  So, reading left to right, I try to match:

1) One or more leading spaces OR
2) One or more trailing spaces OR
3) \n (it's \\n in the pattern because \ is a special character) OR
4) /n OR
5) Any other character besides a-z, 0-9, or a space

If any of the above are found they are replaced with a zero-length string, effectively deleting them.

If that pattern is not quite what you are looking for, then please restate the rules as clearly as you can,
and I can revise it.

Patrick

PAtrick

BTW, I forgot to say thank you, I have improved my function to which I added the presets which I assume are over-ridden by any user selection.  Still to test that bit but I assume it's what they do ... makes things cleaner.

Chris

Hi I am a little confused.  So, I am attaching a sample excel file to see how to apply your programs to my excel file.  In Column C you will find special characters, I would like all the special characters removed in only Column C.  Thanks!!

The attached is a result of adding the function and a sub process C to make the change.

Any new data will be immediately corrected as long as macros are enabled.

Chris

Chris:
The screen flickers, but the data looks the same.  Am I doing something wrong?  I went to F8 and then run ProcessC.  

alt + F* runs a macro but I assume that's what you meant.  It won't make a difference since I already ran it.  Note hoewever as far as I can tell you only wanted to exclude the characters:

:,()

ANd since these were already excluded when I ran it there will be no difference.  If you want to exclude more characters then of course let me know.  Also note that it is now dynamic and will auto exclude the unwanted characters ... try adding one of the four characters to column C and see what happens when you tab or enter.

Chris

Wow!! That's impressive!  I want all these characters excluded: -, ;, !, <,>,~, `,$, #, @, &, *, (, ),?,/,/n,\n, ^, [,],{,},|.  Thanks again.

Excellent Work!!!