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
