Remove All Special Characters

[JADownie]

I have an Excel file (form) with several macros embedded that do a variety of functions. When a user saves their Excel file, I have a macro which creates a CSV copy of the values input so that I can load to my database. From time to time, a user will copy and paste something into the Excel file which has a special character. My database is unable to read files with special characters, so as a result, no CSV file is generated when the user saves. I would like to write a macro that searches all sheets in the file and removes any special non text characters. Does anyone have a macro they currently use for this now. Thank you in advance.

 

[Ron Coderre]

Do you have a list of what's allowed....or not allowed?
Some examples of acceptable and unacceptable values would be helpful, too.

 

[mole999]

have a look at Ricks solution here http://www.mrexcel.com/forum/excel-...-remove-non-alphanumeric-characters-cell.html and see if that can be brought round

 

[JADownie]

Hi,

This seems to work for my Non Alpha Numeric Characters now when I run, but I see that it is also removing any spacing between words thus creating 1 string of text in the cells. Is there a way to maybe have this ignore spaces...


Sub RemoveNonAlphaNumericCharacters()
Dim X As Long, Text As String, Cell As Range, Rng As Range
Set Rng = Range("C3:F9")
For Each Cell In Rng
Text = Cell.Value
For X = 1 To Len(Text)
If Mid(Text, X, 1) Like "[!A-Za-z0-9]" Then Mid(Text, X) = Chr(1)
Next
Cell = Replace(Text, Chr(1), "")
Next
End Sub

 

[mole999]

try disabling Cell = Replace(Text, Chr(1), "") it may be this though I haven't tested it

 

[JADownie]

I disabled Cell = Replace(Text, Chr(1), "") and now no characters are cleaned out...

 

[mole999]

sorry, not my code, have no idea where to tweak this

 

[Rick Rothstein]

Hi,

This seems to work for my Non Alpha Numeric Characters now when I run, but I see that it is also removing any spacing between words thus creating 1 string of text in the cells. Is there a way to maybe have this ignore spaces...


Sub RemoveNonAlphaNumericCharacters()
Dim X As Long, Text As String, Cell As Range, Rng As Range
Set Rng = Range("C3:F9")
For Each Cell In Rng
Text = Cell.Value
For X = 1 To Len(Text)
If Mid(Text, X, 1) Like "[!A-Za-z0-9]" Then Mid(Text, X) = Chr(1)
Next
Cell = Replace(Text, Chr(1), "")
Next
End Sub

See if this works for your...

Sub RemoveNonAlphaNumericCharacters()
  Dim X As Long, Text As String, Cell As Range, Rng As Range
  Set Rng = Range("C3:F9")
  For Each Cell In Rng
    Text = Cell.Value
    For X = 1 To Len(Text)
      If Mid(Text, X, 1) Like "[[COLOR="#FF0000"][B]!A-Za-z0-9[/B][/COLOR]]" Then Mid(Text, X) = " "
    Next
    Cell.Value = Application.Trim(Text)
  Next
End Sub

Note: If there are any other characters you do not want replaced (such as commas, periods, and such), try adding them to the end of the text I highlighted in red BUT if one of those character is a dash, make sure it is the last character at the end.

 

[JADownie]

This code seems to work well now for me. Last question now... Is there a way to alter so that I do not need to have an exact range like C3:F9 but instead all active cells on the sheet

 

[Rick Rothstein]

Is there a way to alter so that I do not need to have an exact range like C3:F9 but instead all active cells on the sheet

This should work...

Sub RemoveNonAlphaNumericCharacters()
  Dim X As Long, Text As String, Cell As Range, Rng As Range
  For Each Cell In ActiveSheet.UsedRange
    Text = Cell.Value
    For X = 1 To Len(Text)
      If Mid(Text, X, 1) Like "[!A-Za-z0-9]" Then Mid(Text, X) = " "
    Next
    Cell.Value = Application.Trim(Text)
  Next
End Sub