Hey thanks, Gord! ...that definately kills the ' in my cells.

Got a question tho: you say that this should strip all characters in
cell except for numbers and the decimal point .... which is exactl
what I really need -- I was looking to trim the cell.Value from eithe
side, but you thought to just filter out unwanted characters from th
string. Thanks for the insight.

However- I found that your macro returns [150.234in] from ['150.234 in
.... leaving the "in" ... so not filtering letters. <?

I've studied your macro but I'm so green at this that I don't kno
where to begin tweaking it.

Thanks again, and in advance.
loberg


Gord Dibben wrote:
[b]loberg

This macro will strip all but numbers and the decimal point.

Well, not quite.......it will also leave /(asc 47) if present. Yo
don't have
any of these /'s in your example so no problem<g

Took about 15 seconds to convert 222,000 cells on 2.6Ghz machine

Public Sub StripAllAZs()
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))) < 48) Or _
(Asc(UCase(Mid(myStr, i, 1))) 57) Then
myStr = Left(myStr, i - 1) & " " & Mid(myStr, i + 1)/1000
End If
Next i
cell.Value = Application.Trim(myStr)
Next cell
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End If
With Application
.Calculation = xlAutomatic
.ScreenUpdating = True
End With
End Sub

Gord Dibben XL200

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