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

You could use this UDF. It will return all of the digits in the string in the
cell.

You enter the formula:

=reNums(str) into some cell where str is either the string containing some
numbers or a cell reference.

To enter the UDF in VBA, <alt-F11 opens the VBEditor. Ensure your project is
highlighted in the Project Explorer window, then Insert/Module and paste the
code below into the window that opens:

==========================
Option Explicit
Function reNums(str As String)
Dim re As Object
Const sPat As String = "\D"
Set re = CreateObject("vbscript.regexp")
re.Pattern = sPat
re.Global = True
reNums = re.Replace(str, "")
End Function
========================
--ron