Delete all special characters in a selection?

  • Thread starter Thread starter ali
  • Start date Start date
A

ali

Does anyone know ofa macro that when run will clear all 'special'
characters out of a cell or selection of cells leaving just numbers or
text characters behind?

Thanks
 
It's not hard to write one, though I'm sure it's not the moset
efficient:

Public Sub CleanText()
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
 
If you are talking about cleaning up data that has been imported from the web,
then you might want to take a look at Dave McRitchie's Trimall macro which will
zap that garbage but good!!

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

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
Newsgroups - Where you really can get a free lunch!!
----------------------------------------------------------------------------



ali said:
Does anyone know ofa macro that when run will clear all 'special'
characters out of a cell or selection of cells leaving just numbers or
text characters behind?

Thanks


------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to creating
Click to expand...
financial statements
 
David's excellent macro zaps extra spaces and non-breaking-spaces
CHAR(160), but doesn't remove any other characters, nor does it
remove single spaces between other characters.

Guess it depends on what the OP considers "special".
 
Back
Top