separating numbers from text

[SIAMAK]

Hi,
with thanks from Bob,Rick,Ron and Suleman , I think I did not ask my
question clearly . let I write it again as an example :
cell include reply cell
including formula
___ __________ _____
____________ ________
A1 MR FRANK (25687) --> B1
25687 ?
A2 MR FRANK 256875787 --> B2 256875787
?
A3 MR FRANK (256875) AT2008/2/6 --> B3 256875
?
A4 MR FRANK 2560 IN U.S --> B4 2560
?
A5 25602MR FRANK --> B5
25602 ?

please send for me the correct formulas that I shoude use in the cells from
B1 to B5 till I can get the above replies.

thank you,

 

[Mike H]

Hi,

You got an answer from Bob Phillips that does precisely what you want. Have
another look.

http://www.microsoft.com/office/com...d060&cid=f71bc3ee-5488-464c-b50a-4d4a2563aa93

Mike

 

[Ron Rosenfeld]

Hi,
with thanks from Bob,Rick,Ron and Suleman , I think I did not ask my
question clearly . let I write it again as an example :
cell include reply cell
including formula
___ __________ _____
____________ ________
A1 MR FRANK (25687) --> B1
25687 ?
A2 MR FRANK 256875787 --> B2 256875787
?
A3 MR FRANK (256875) AT2008/2/6 --> B3 256875
?
A4 MR FRANK 2560 IN U.S --> B4 2560
?
A5 25602MR FRANK --> B5
25602 ?

please send for me the correct formulas that I shoude use in the cells from
B1 to B5 till I can get the above replies.

thank you,

Yes, asking your question clearly is important in obtaining the help you wish.

It's still not entirely clear, but it appears that you wish to extract the
first series of digits in each string.

I have also assumed that the A1, A2, etc at the beginning of each string are
cell references and not part of the string.

That being the case, you could use this UDF:

=ExtrFirstNum(A1)

with this code entered as before:

============================
Option Explicit
Function ExtrFirstNum(str As String)
Dim re As Object, mc As Object
Set re = CreateObject("vbscript.regexp")
re.Global = False
re.Pattern = "\d+"
If re.test(str) = True Then
Set mc = re.Execute(str)
ExtrFirstNum = mc(0)
End If
End Function
==============================
--ron