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.

 

[Juglaz]

1) There are two numbers here. Do you want to extract both? Will there always be two?
2) Will the numbers always follow the same format? (ie 11 characters with no -'s)
3) If the numbers will come in different formats, show the formats that the macro should be able to handle.
4) How do you want the numbers reported? What if there are multiple numbers?

 

[Preond]

1) There can be multiple numbers and it would be great i can get both the number's.
2) yes.
3) only 1 format.
4) separated by ","

Thank you for your assistance.

 

[Rick Rothstein]

Hi,

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

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

1. Will your numbers always be listed next to each other (delimited with commas) as shown?

2. If the answer to #1 is "yes", then will those numbers always follow the words "Contact. No:-" (with the period after the word "Contact" as shown and with the ":-" after the word "No" also as shown)?

 

[taurean]

I think those are Indian mobile numbers. So if any one of the mobile numbers is fine then following can be used assuming the first digit will always be 0.
="0"&MAX(IFERROR(--MID(B2,ROW($A$1:$A$999),10),0))
To be array entered [CTRL + SHIFT + ENTER]

If the numbers are either 10/11 digits with a possibility of first non zero number.
=MID(B2,FIND(MAX(IFERROR(--MID(B2,ROW($A$1:$A$999),11),0)),B2,1)-(LEN(MAX(IFERROR(--MID(B2,ROW($A$1:$A$999),11),0)))=10),11)

 

[Rick Rothstein]

I think those are Indian mobile numbers. So if any one of the mobile numbers is fine then following can be used assuming the first digit will always be 0.
="0"&MAX(IFERROR(--MID(B2,ROW($A$1:$A$999),10),0))
To be array entered [CTRL + SHIFT + ENTER]

If the numbers are either 10/11 digits with a possibility of first non zero number.
=MID(B2,FIND(MAX(IFERROR(--MID(B2,ROW($A$1:$A$999),11),0)),B2,1)-(LEN(MAX(IFERROR(--MID(B2,ROW($A$1:$A$999),11),0)))=10),11)

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

 

[Juglaz]

Can you explain what the -- does in front of your MID? Sorry if I'm going off topic here. Don't mean to derail the discussion.

 

[Preond]

Hi Rick,

1. it will be either delimited with a comma or a space,
2. No, the numbers might follow the words like Mobile No. OR Phone No.

Thank you for your assistance.

 

[ANALYSTBANK]

Is it alphanumeric? I am unable to view the file, as i've excel2003. I've function that can extract the numeric data from a cell containing alphanumeric data.

You can look here also

Extracting numbers from alphanumeric strings - Excel - Office.com

 

[franklingroove]

A simple regex can give the solution..

"Can someone please tell me how to get the code window, still cant figure it out."

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