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

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

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

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

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