Removing special characters and spaces

[Ralph K.]

I have about 12,000 rows of data that contain special characters like
commas, quotes, ampersands, etc. I would like to clean up the data by
removing all the special characters. Any ideas?
Also, once that is done, I want to remove all the spaces. The TRIM
function won't remove spaces between words. Any idea about this
problem?
Thanks in advance for any suggestions.

Ralph

 

[Frank Kabel]

Hi
see:
http://www.mvps.org/dmcritchie/excel/join.htm#trimall

 

[John Mansfield]

Hi,

To get rid of the special characters, you can go to EDIT -
REPLACE. In the EDIT field, enter the character that you
want to get rid of. Then, leave the REPLACE field blank.

John Mansfield

 

[Gord Dibben]

Ralph

Get rid of everything but numbers or text. Also strips all spaces.

Public Sub StripAll_But_NumText()
Dim rConsts As Range
Dim rcell As Range
Dim i As Long
Dim sChar As String
Dim sTemp As String

On Error Resume Next
Set rConsts = Selection.SpecialCells(xlCellTypeConstants)
On Error GoTo 0
If Not rConsts Is Nothing Then
For Each rcell In rConsts
With rcell
For i = 1 To Len(.text)
sChar = Mid(.text, i, 1)
If sChar Like "[0-9a-zA-Z]" Then _
sTemp = sTemp & sChar
Next i
.Value = sTemp
End With
sTemp = ""
Next rcell
End If
End Sub

Gord Dibben Excel MVP

 

[Ralph K.]

Thank you all for the suggestions.

Ralph