Removing special characters and spaces

  • Thread starter Thread starter Ralph K.
  • Start date Start date
R

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
 
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
 
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
 
Back
Top