Thanks Rick


Gord

On Sun, 21 Oct 2007 02:14:15 -0400, "Rick Rothstein \(MVP - VB\)"
wrote:

I think your function would be better off without the decimal points being
included in the pattern strings for the two Like comparison statements
(otherwise text having periods in them, but with no digits, will return 0
instead of an easily checkable #VALUE! error).

Rick

"Gord Dibben" <gorddibbATshawDOTca wrote in message
.. .
I find it handy to use a User Defined Function.

Function DeleteNonNumerics(ByVal sStr As String) As Long
'returns numbers with decimal points if any
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

Usage is: =deletenonnumerics(A1)

Copy/paste the UDF above into a General Module in your workbook.

If not familiar with macros and VBA, visit David McRitchie's website on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the above code in there. Save the
workbook and hit ALT + Q to return to your workbook.


Gord Dibben Excel MVP

On Sat, 20 Oct 2007 13:41:04 -0700, Michel

wrote:

Hi,

I need to get a number out of a cell with text. How should I make the
formula ?

Eg. :

A1 A2 (RESULT)
hjjkhkjkhkj1000-841jkjkjrkjrkj 1000841

Many thanks in advance!

Michel