I WANT TO SEPARATE NUMBERS FROM TEXT IN A CELL ; LIKE:
A1: MR FRANK (25687)
A2: MR FRANK 256875787
A3: MR FRANK (256875) AT2008/2/6
A4: MR FRANK 2560 IN U.S
A5: 25602MR FRANK
THANK YOU.

=LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0 123456789")),ROW(INDIRECT("1:"&LEN(A1))))))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"SIAMAK" wrote in message
...
I WANT TO SEPARATE NUMBERS FROM TEXT IN A CELL ; LIKE:
A1: MR FRANK (25687)
A2: MR FRANK 256875787
A3: MR FRANK (256875) AT2008/2/6
A4: MR FRANK 2560 IN U.S
A5: 25602MR FRANK
THANK YOU.

What do you mean by "separate numbers from text in a cell"? Did you want the
text deleted and the remaining numbers left in the same cell? Did you want
the numbers deleted and the text to remain in the same cell? Did you want
the text to remain in the same cell and the numbers placed in some other
cell (or vice-versa)? Or, perhaps, something else entirely? Also, so we
don't have to guess, whatever your answer is to the above questions, what do
you expect A3 to look like afterwards?

For future reference, please don't post in all-caps... it is very hard to
read that way. Also, when you give an example of your "existing" data, it is
usually a good idea to show us how you expect it to look afterwards.

Rick


"SIAMAK" wrote in message
...
I WANT TO SEPARATE NUMBERS FROM TEXT IN A CELL ; LIKE:
A1: MR FRANK (25687)
A2: MR FRANK 256875787
A3: MR FRANK (256875) AT2008/2/6
A4: MR FRANK 2560 IN U.S
A5: 25602MR FRANK
THANK YOU.

On Thu, 17 Jul 2008 04:27:00 -0700, SIAMAK
wrote:

I WANT TO SEPARATE NUMBERS FROM TEXT IN A CELL ; LIKE:
A1: MR FRANK (25687)
A2: MR FRANK 256875787
A3: MR FRANK (256875) AT2008/2/6
A4: MR FRANK 2560 IN U.S
A5: 25602MR FRANK
THANK YOU.

Your request is open to varying interpretations.

Here are some VBA solutions.

Enter the UDF below.
<alt-F11 opens the VB Editor
Ensure your project is highlighted in the project explorer window, then
Insert/Module and paste the code below into the window that opens.

You can then use the formula: =ReSub(cell_ref, Pattern) to "remove" what you
don't want (this is more efficient than returning what you do.

For example:

=resub(A1,"\D+") will remove everything that is NOT a digit, returning only
digits. So:

A1: MR FRANK (25687) -- 125687
A2: MR FRANK 256875787 -- 2256875787
A3: MR FRANK (256875) AT2008/2/6 -- 3256875200826
A4: MR FRANK 2560 IN U.S -- 42560
A5: 25602MR FRANK -- 525602

To remove the digits, returning only NON-digits, use "\d+" for the second
argument. =resub(A1,"\d+")

A1: MR FRANK (25687) -- MR FRANK ()
A2: MR FRANK 256875787 -- MR FRANK
A3: MR FRANK (256875) AT2008/2/6 -- MR FRANK () AT//
A4: MR FRANK 2560 IN U.S -- MR FRANK IN U.S
A5: 25602MR FRANK -- MR FRANK

To remove everything that is not a letter or a <space, returning only letters
and <spaces use "[^A-Za-z\s]+" for the second argument:
=resub(A1,"[^A-Za-z\s]+")

A1: MR FRANK (25687) -- MR FRANK
A2: MR FRANK 256875787 -- MR FRANK
A3: MR FRANK (256875) AT2008/2/6 -- MR FRANK AT
A4: MR FRANK 2560 IN U.S -- MR FRANK IN US
A5: 25602MR FRANK -- MR FRANK

If you want something else, you'll need to be more specific.




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

Hi,

If you have the number of characters limited including space then try this
=MID(A3,9,20)
For eg. MR Frank is 8 digits including space if all the names are of 8
digits then you can use this. (20) actually refers to the number of character
that excel will return you can change it if you feel the characters are not
complete.
--
Thanks
Suleman Peerzade


"SIAMAK" wrote:

I WANT TO SEPARATE NUMBERS FROM TEXT IN A CELL ; LIKE:
A1: MR FRANK (25687)
A2: MR FRANK 256875787
A3: MR FRANK (256875) AT2008/2/6
A4: MR FRANK 2560 IN U.S
A5: 25602MR FRANK
THANK YOU.