Thanks for everyone's help. You are the most awesomeest (if that's a word).
I used the DataText to ColumnsNextNextAdavnced and "trailing minus
signs" ithat you suggested and it worked perfectly.

Thanks.

"Gord Dibben" wrote:

Read Earl's post first then if you need a macro...................

Sub RemoveAlphas()
Dim intI As Integer
Dim rngR As Range, rngRR As Range
Dim strNotNum As String, strTemp As String

Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues)

For Each rngR In rngRR
strTemp = ""
For intI = 1 To Len(rngR.Value)
If Mid(rngR.Value, intI, 1) Like "[0-9]" Then
strNotNum = Mid(rngR.Value, intI, 1)
Else: strNotNum = ""
End If
strTemp = strTemp & strNotNum
Next intI
rngR.Value = strTemp
Next rngR
End Sub


Gord Dibben MS Excel MVP


On Wed, 6 Sep 2006 14:01:02 -0700, David T
wrote:

I am trying to come up with a "Do until Loop" macro to find and remove
non-numeric characters from all cells in a column, leaving only the numbers.
For example, if cell b6 contains
"$6,800 - ", I would like the macro to delete the "-" so that the cell will
have a value of -6,800.

I need the macro to Loop until the last row. I've tried to come up with a
macro but have not been successful. So far i have come up with this but it
stops me at the "Find" in my formula.

Sub CalculateOA()
Dim i As Integer

i = 5
Do Until [last row, what is the formula?]

Cells(i, 11).Value = Left(Cells(i, 11), (Find(("-"), Cells(i, 11)) - 1))
* -1

i = i + 1
Loop


Please help