Extract two sets of seven digit numbers from one cell

[newyorknix]

Hi,
I have many cells with alphanumeric text which contains two or three {seven digit numbers} and one or two {nine digit numbers} like this

Clark+(74)-(365)-4675837, 4839057, 47589305 +(74)-9933457364, 9485265483 Near Green Chapel, Engineers Avenue

I want to extract first seven digit number 4675837 in one cell and the next 4839057 in another cell, etc.,

I tried methods like left, right and mid as given in forum, but I have two set of seven digit numbers which is the problem.

Can anyone help me on this issue?
Thanks & Regards,
Nicks

 

[Scott Huish]

Hi, welcome to the board!

Try this UDF:

Function GetSeven(r As Range, occurrance As Long) As String
Dim s As String
s = r.Text
With CreateObject("vbscript.regexp")
    .Pattern = "\d{7}"
    .Global = True
    If .test(s) Then
        Set m = .Execute(s)
        GetSeven = m(occurrance - 1)
    End If
End With
End Function

Then in the spreadsheet like this:
Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 738px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>Clark+(74)-(365)-4675837, 4839057, 47589305 +(74)-9933457364, 9485265483 Near Green Chapel, Engineers Avenue</TD><TD style="TEXT-ALIGN: right">4675837</TD><TD style="TEXT-ALIGN: right">4839057</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; BORDER-LEFT-STYLE: groove; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B1</TD><TD>=getseven(A1,1)</TD></TR><TR><TD>C1</TD><TD>=getseven(A1,2)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

 

[newyorknix]

Thank you so much Hotpepper...
That is very helpful and I am very sorry for the late reply...
It worked perfectly (with the fact that just now I searched and knew about UDF)
But there are some cells which only have mobile numbers, that is nine digits... When I apply the method, this results in seven digits mobile number output like 9847599
Can I somehow avoid it? (like restricting all the numbers starting with 9 i.e.,) mobile numbers start with nine and landline numbers start with 2 or 6...
Thanks again
Regards,
Nicks

 

[Scott Huish]

See if this works for you:

Function GetSeven(r As Range, occurrance As Long) As String
Dim s As String
s = r.Text
With CreateObject("vbscript.regexp")
    .Pattern = "\b\d{7}\b"
    .Global = True
    If .test(s) Then
        Set m = .Execute(s)
        If m.Count >= occurrance Then GetSeven = m(occurrance - 1)
    End If
End With
End Function

 

[newyorknix]

Hi,
Thanks for the reply
After updating new UDF, all of the output turned #NAME?
Regards,
Nicks

 

[Scott Huish]

Did you put it in a standard module?

 

[newyorknix]

No... I guess...
I am very new to this stuff
Searching for a link to know how to add standard...
Thank you so much for the help

 

[newyorknix]

Its working Hotpepper...

Currently I am extracting the seven digit numbers first and then changing the UDF value to extract 10 digit numbers...
Is there a way to combine?


Also as of now, if there are no numbers, it gives #value error. Can we just have it blank if the value is unavailable?

Thanks & Regards,
Nicks