Public Sub StripAllButAZs()
''strips out everything except letters
Dim myRange As Range
Dim Cell As Range
Dim myStr As String
Dim i As Integer

With Application
.ScreenUpdating = False
.Calculation = xlManual
End With
On Error Resume Next
Set myRange = Range(ActiveCell.Address & "," & Selection.Address) _
.SpecialCells(xlCellTypeConstants)
If myRange Is Nothing Then Exit Sub
If Not myRange Is Nothing Then
For Each Cell In myRange
myStr = Cell.text
For i = 1 To Len(myStr)
If (Asc(UCase(Mid(myStr, i, 1))) < 65) Or _
(Asc(UCase(Mid(myStr, i, 1))) 90) Then
myStr = Left(myStr, i - 1) & " " & Mid(myStr, i + 1)
End If
Next i
Cell.Value = Application.Trim(myStr)
Next Cell
End If
With Application
.Calculation = xlAutomatic
.ScreenUpdating = True
End With
End Sub


Gord Dibben Excel MVP

On Fri, 2 Dec 2005 11:34:09 -0600, Scorpvin
wrote:


Column A has characters such as - ' . @ # ? " ( ) % ^ & + How do I
remove all types of characters in this column? The only thing I want
is the text.