Delete all special characters in a selection?
 

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


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements

Delete all special characters in a selection?
 

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



In article ,
ali wrote:

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

Delete all special characters in a selection?
 

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" wrote in message
...

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


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to creating
financial statements


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.547 / Virus Database: 340 - Release Date: 02/12/2003

Delete all special characters in a selection?
 

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".



In article ,
"Ken Wright" wrote:

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

Delete all special characters in a selection?
 

Very true - Thanks for the clarification JE.

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

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



"J.E. McGimpsey" wrote in message
...
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".



In article ,
"Ken Wright" wrote:

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



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.547 / Virus Database: 340 - Release Date: 02/12/2003