extract phone numbers

[vikas_newports]

How can I extract only the phone number from Alphanumeric string? Ideal length of phone number is more than 6 to 15 digits , 2 or more phone numbers can be found in a cell too

IANOS PERLA HARGHITEI 0724212149 0266212149

IANOS Perla HARGHITEI 0724212149 0266212150
MATEI BALASA MARAMURESENI 0742200007 RUSTIC CONSTRUCT J241542000 R 12732816
MEDIDENT BV IANOVICI FLORIN 07445657888 medidex@rdslinkro
oneda maria 0744629962
Geberit CARMEN NACLAD 3303080 fax 3303161
Velea Cristian +40 728 664 979 - 30 personae Botez - trimis mieniu 220 lei All inclusive - a vazut dej alocatia Azi 12.02.2019
Cerere - 0733375505 - Marinescu Cosmin = 2000 lei -( posibil 2500) 30 persoane
Murgoci Daniel - 0765728818 - botez 35 persoane - bogdan
eneea - marian tanase 40 73 310 9926
parvu catalin botez ,20 persoane abc@yahoo.com 0727164124

 

[vikas_newports]

maras, I used your function which is looking good but I think further I need to use text to column or other formulas to extract only numbers from UDF result​

 

[maras]

From the spreadsheet use Alt + F11 -> Insert -> Module and enter the code here.

 

[sandy666]

Thanks sandy , I have updated my profile, and On 7th row there is data but I do not need it I am stuck at only date , Date element is not common so I will manually handle the date cells

Thanks for updating,

it depends on how many rows you have
with ten thousand rows it will be hard work

btw. try not to yell at us with bold text

 

[vikas_newports]

Sandy, Yes you are right data can be variable and increase decrease depends upon the source.

 

[sandy666]

I see you got solution with regex so good luck and have a nice day

 

[maras]

@vikas_newports, if there are only phone numbers, then such a UDF.

Function PhoneNumbers(zak As Range) As String
    Dim clm As Object, c As Object
    Dim i As Integer
    Dim ms As String, nr As String
    
    PhoneNumbers = "": ms = ""
    With CreateObject("VBScript.RegExp")
        .Pattern = "[0-9 +]{8,}"
        Set clm = .Execute(zak.Value)
        For i = 0 To clm.Count - 1
            nr = Trim(clm.Item(i).Value)
            ms = ms & IIf(i = clm.Count - 1, nr, nr & " ")
        Next
        PhoneNumbers = ms
    End With
    Set clm = Nothing
End Function

 

[vikas_newports]

Thanks its good and Almost near to my problem's solution

@vikas_newports, if there are only phone numbers, then such a UDF.

Function PhoneNumbers(zak As Range) As String
    Dim clm As Object, c As Object
    Dim i As Integer
    Dim ms As String, nr As String
   
    PhoneNumbers = "": ms = ""
    With CreateObject("VBScript.RegExp")
        .Pattern = "[0-9 +]{8,}"
        Set clm = .Execute(zak.Value)
        For i = 0 To clm.Count - 1
            nr = Trim(clm.Item(i).Value)
            ms = ms & IIf(i = clm.Count - 1, nr, nr & " ")
        Next
        PhoneNumbers = ms
    End With
    Set clm = Nothing
End Function