Until someone comes up with a better answer.....

=IF(ISERROR(deletenonnumerics(J6)),"",deletenonnum erics(J6))


Gord

On Thu, 1 Dec 2005 11:51:02 -0800, Dennis
wrote:

Gord,

Best way to avoid an error in the function if the cell does not have any
numerics?

Thanks for your knowledge and time!

Dennis




"Gord Dibben" wrote:

Corey

Suggest a UDF if you want a formula to extract numbers to another cell and
leave original data in place.

Function DeleteNonNumerics(ByVal sStr As String) As Long
Dim i As Long
If sStr Like "*[0-9]*" Then
For i = 1 To Len(sStr)
If Mid(sStr, i, 1) Like "[0-9]" Then
DeleteNonNumerics = DeleteNonNumerics & Mid(sStr, i, 1)
End If
Next i
Else
DeleteNonNumerics = sStr
End If
End Function

If you want to strip in place use a macro after selecting the column.

Sub RemoveAlphas()
'' Remove alpha characters from a string.
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 Excel MVP


On Thu, 1 Dec 2005 09:26:02 -0800, "Corey"
wrote:

Hello. I have a column with data that contain numerics and text:

ON 3127
ON2679
O/F 20R
OFF///130H

Is there a formula I can input in an adjacent cell to only pull out the
numbers to give me the following:

3127
2679
20
130

Any help is appreciated. Thanks!