Extracting phone numbers from a word string.

[Preond]

Hi,

Need assistance in finding solution on, how to extract phone number from a text string.

http://goo.gl/qucPe7(Link for test file)


I have searched the forum but was not able to find any solution which can help.

Thanks in Advance.

 

[Preond]

Hi Ananlystbank,

that works if you have extract number from one location.

 

[franklingroove]

Sub FindNumbers()
 Dim ob As Object
 Set ob = CreateObject("vbscript.regexp") ' i am using excel 2007
  With ob
   .Pattern = "\b\d{11}\b"
   .Global = True
 Set matches = .Execute(Range("b2")) 'where ur string contains
    r = 3                           'where you want to paste the value
      For Each Match In matches
      Cells(r, 2).Value = Match
      r = r + 1
      Next Match
  End With

 End Sub

 

[Preond]

Hi Franklingroove,

it is Alt+F11

 

[franklingroove]

Hi Preond, I meant the code window on this site but thanks I figured it out.
Did you tried the macro I gave you, it works here at my end. It will retrieve all the 11 digit numbers from the string(which is in b2 in my macro) and will start putting it from range b3 down below depending on how many numbers you got.
Give it a go

 

[Rick Rothstein]

Okay, here is a non-RegExp UDF (user defined function) that you can use to retrieve a comma/space delimited list of all 11-digit numbers in the cell's text passed into it...

Function PhoneNumbers(ByVal S As String) As String
  Dim X As Long
  For X = 1 To Len(S)
    If Mid(S, X, 11) Like "###########" Then PhoneNumbers = PhoneNumbers & ", " & Mid(S, X, 11)
  Next
  PhoneNumbers = Mid(PhoneNumbers, 3)
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use PhoneNumbers just like it was a built-in Excel function. For example,

=PhoneNumbers(A1)

 

[taurean]

How will either of these make a comma separated list (see Item #4 in Message #3)?

Hi Rick, below is what I said [if acceptable to OP]:

So if any one of the mobile numbers is fine

 

[Preond]

Hi Rick,

thank you...it works brilliantly.

Regards,

Prem tourani.