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

 

[sandy666]

like this?

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

 

[Fluff]

Cross posted Extract Phone Number from Cell

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered elsewhere.

 

[vikas_newports]

Thanks Fluff
Meanwhile I posted the same question on other Help Forum Link is Here Other Forum Link

 

[vikas_newports]

Sandy, It is taking extra numbers too I also approached it with below formulas

=IF(SUM(LEN(A2)-LEN(SUBSTITUTE(A2, {"0","1","2","3","4","5","6","7","8","9"}, "")))>0, SUMPRODUCT(MID(0&A2, LARGE(INDEX(ISNUMBER(--MID(A2,ROW(INDIRECT("$1:$"&LEN(A2))),1))* ROW(INDIRECT("$1:$"&LEN(A2))),0), ROW(INDIRECT("$1:$"&LEN(A2))))+1,1) * 10^ROW(INDIRECT("$1:$"&LEN(A2)))/10),"")

OR

=SUMPRODUCT(MID(0&A2, LARGE(INDEX(ISNUMBER(--MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1)) * ROW(INDIRECT("1:"&LEN(A2))), 0), ROW(INDIRECT("1:"&LEN(A2))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(A2)))/10)

 

[sandy666]

your only criterion is : between 6 and 15 so dates with 8 digits are treated as phone number, not all numbers contain leading zero
so maybe much more details or defined pattern

btw. I don't use formulas

 

[vikas_newports]

Sandy, Sorry for the wrong illustration from my end. phone number can be between from 6 to 15 and sometime 2 phone numbers can be found in a cell ,
I am not good at excel but learning at my own pace , Might be you are using Power Query ..

 

[sandy666]

you are using Power Query

right
so maybe update your profile (Account details) about excel version and OS don't forget to scroll down and hit save

first three lines contain "phone" numbers separated by space but the seventh line contains phone number and date which is treated as a phone number according to your criteria
you are the only one who can tell what is and what is not a phone number

 

[maras]

Check the UDF function.
Use in sheet e.g. = PhoneNumbers (A1)

Function PhoneNumbers(zak As Range) As String
    Dim clm As Object
    Dim i As Integer
    Dim ms As String, nr As String
   
    PhoneNumbers = "": ms = ""
    With CreateObject("VBScript.RegExp")
        .Pattern = "(^\D+)(?=\d+.*)"
        .Global = True
        .IgnoreCase = True
        Set clm = .Execute(zak.Value)
        ms = clm.Item(0).submatches(0)
        .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 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