Sub CleanCells()
Dim sStr as String
Dim cell as Range
for each cell in selection
if not cell.hasFormla then
sStr = Application.Substitute(cell.value,chr(160)," ")
sStr = Application.Clean(sStr)
sStr = Application.Trim(sStr)
cell.Value = sStr
end if
Next
End Sub

--
Regards,
Tom Ogilvy

"JJ" wrote in message
...
I want to remove ANY characters there are not alphanumeric (not to include
spaces or hyphens, I still need those). These cells will not have
formulas
at the time I'm cleaning up the cells. Since I pull from different
databases, I run into a problem when I compare the cell values - which I
need
to do from the right. If there is a character that takes a space they
won't
match and because this character is not appearing in every cell or even in
every column I can't just say ignore the first right character.

Does that make sense?

"David McRitchie" wrote:

are you sure you want to use CLEAN.

The use of the CLEAN Worksheet Function to remove unprintable characters
will only remove CHAR(0) through CHAR(31), Char(129), Char(141),
Char(143),
and Char(144). It will have no effect on Char(160).

When you apply the function to all cells in a workbook you would wipe
out
formulas; remove linebreaks closing up words that won't even have a
space
between them.

Suggest reading the paragraphs before and within the TRIMALL macro
description on my page.
http://www.mvps.org/dmcritchie/excel/join.htm#trimall
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"JJ" wrote ...
Is there a way I can apply the clean function to my whole spreadsheet?