any one correct this single formula to extract 10 digits mobile number

[Prasad K]

this single formula is working there is a problem this formula is extracting only one 10 digit Mobile from starting i want to extract all 10 digit number to separate cells


=MID($A$1,(FIND("Phone:",$A$1)+LEN("Phone:"))+1,(FIND(" ",$A$1,(FIND("Mobile:",$A$1)+LEN("Mobile:"))+1)-(FIND("Mobile:",$A$1)+LEN("Mobile:")+1)))

dup.xlsm
	A	B	C	D	E
1	prasad Phone: 9632587410 vishnu Mobile: 8521479630 reddy Mobile: 6541239870	9632587410	9632587410	9632587410
2	prasad Phone: 9632587410 vishnu Mobile: 8521479630 reddy Mobile: 6541239870	9632587410	9632587410	9632587410
3	prasad Phone: 9632587410 vishnu Mobile: 8521479630 reddy Mobile: 6541239870 bobby Phone: 7413698521	9632587410	9632587410	9632587410	9632587410
Sheet5

Cell Formulas
Range	Formula
B1:D1	B1	=MID($A$1,(FIND("Phone:",$A$1)+LEN("Phone:"))+1,(FIND(" ",$A$1,(FIND("Mobile:",$A$1)+LEN("Mobile:"))+1)-(FIND("Mobile:",$A$1)+LEN("Mobile:")+1)))
B3:E3,B2:D2	B2	=MID($A$2,(FIND("Phone:",$A$2)+LEN("Phone:"))+1,(FIND(" ",$A$2,(FIND("Mobile:",$A$2)+LEN("Mobile:"))+1)-(FIND("Mobile:",$A$2)+LEN("Mobile:")+1)))

 

[Peter_SSs]

Try this

21 09 05.xlsm
	A	B	C	D	E	F
1	prasad Phone: 9632587410 vishnu Mobile: 8521479630 reddy Mobile: 6541239870	9632587410	8521479630	6541239870
2	prasad Phone: 9632587410 vishnu Mobile: 8521479630 reddy Mobile: 6541239870	9632587410	8521479630	6541239870
3	prasad Phone: 9632587410 vishnu Mobile: 8521479630 reddy Mobile: 6541239870 bobby Phone: 7413698521	9632587410	8521479630	6541239870	7413698521
Phone

Cell Formulas
Range	Formula
B1:F3	B1	=LEFT(TRIM(MID(SUBSTITUTE(SUBSTITUTE($A1,"Mobile","Phone"),"Phone: ",REPT(" ",100)),100*COLUMNS($B:B),100)),10)

 

[Prasad K]

Try this

21 09 05.xlsm
	A	B	C	D	E	F
1	prasad Phone: 9632587410 vishnu Mobile: 8521479630 reddy Mobile: 6541239870	9632587410	8521479630	6541239870
2	prasad Phone: 9632587410 vishnu Mobile: 8521479630 reddy Mobile: 6541239870	9632587410	8521479630	6541239870
3	prasad Phone: 9632587410 vishnu Mobile: 8521479630 reddy Mobile: 6541239870 bobby Phone: 7413698521	9632587410	8521479630	6541239870	7413698521
Phone

Cell Formulas
Range	Formula
B1:F3	B1	=LEFT(TRIM(MID(SUBSTITUTE(SUBSTITUTE($A1,"Mobile","Phone"),"Phone: ",REPT(" ",100)),100*COLUMNS($B:B),100)),10)

Thank you Peter working perfectly

one more small doubt why my formula is extracting only one mobile number

 

[Peter_SSs]

Thank you Peter working perfectly

You're welcome.

one more small doubt why my formula is extracting only one mobile number

In every cell your formula is finding the first "Phone:" then starts 7 characters after that and goes until it finds a space. That would always be the first 'Phone" number.
There is another unrelated error in that your row 3 formula is actually extracting the first number from the row 2 data.

 

[Prasad K]

You're welcome.


In every cell your formula is finding the first "Phone:" then starts 7 characters after that and goes until it finds a space. That would always be the first 'Phone" number.
There is another unrelated error in that your row 3 formula is actually extracting the first number from the row 2 data.

oh Thank you

and more small help from you i got a vba function extract numbers here is a problem this function will extract all numbers into one column only kindly please i request you change this extract numbers into separate columns

dup.xlsm
	A	B
1	prasad Phone: 9632587410 vishnu Mobile: 8521479630 reddy Mobiles: 6541239870 bobby Phone: 7413698521 Reddy Mobile: 6541239871	9632587410 8521479630 6541239870 7413698521 6541239871
2	prasad Phone: 9632587410 vishnu Mobile: 8521479630 reddy Mobiles: 6541239870 bobby Phone: 7413698521 Reddy Mobile: 6541239871
Sheet6

Cell Formulas
Range	Formula
B1	B1	=StrPhone(A1)

Function StrPhone(strIn As String) As String
    Dim objRegexp As Object
    Set objRegexp = CreateObject("VBScript.Regexp")
    With objRegexp
        .Global = True
        .Pattern = ".*?(\d{10})|.*$"
        StrPhone = Trim(.Replace(strIn, "$1 "))
    End With
End Function

 

[navic]

Here is one short formula

=LEFT(TRIM(MID(SUBSTITUTE(" " & $A1,": ",REPT(" ",100)),COLUMNS($B$1:B1)*100,100)),10)

 

[Prasad K]

Here is one short formula

=LEFT(TRIM(MID(SUBSTITUTE(" " & $A1,": ",REPT(" ",100)),COLUMNS($B$1:B1)*100,100)),10)

Thankyou can you change above VBA function as per my request

 

[Peter_SSs]

Here is one way with a UDF

Function Phone(s As String, n As Long) As String
  With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "\d{10}"
    If n <= .Execute(s).Count Then Phone = .Execute(s)(n - 1)
  End With
End Function

Prasad K.xlsm
	A	B	C	D	E	F
1	prasad Phone: 9632587410 vishnu Mobile: 8521479630 reddy Mobile: 6541239870	9632587410	8521479630	6541239870
2	prasad Phone: 9632587410 vishnu Mobile: 8521479630 reddy Mobile: 6541239870	9632587410	8521479630	6541239870
3	prasad Phone: 9632587410 vishnu Mobile: 8521479630 reddy Mobile: 6541239870 bobby Phone: 7413698521	9632587410	8521479630	6541239870	7413698521
Sheet1

Cell Formulas
Range	Formula
B1:F3	B1	=Phone($A1,COLUMNS($B:B))

 

[Prasad K]

Here is one way with a UDF

Function Phone(s As String, n As Long) As String
  With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "\d{10}"
    If n <= .Execute(s).Count Then Phone = .Execute(s)(n - 1)
  End With
End Function

Prasad K.xlsm
	A	B	C	D	E	F
1	prasad Phone: 9632587410 vishnu Mobile: 8521479630 reddy Mobile: 6541239870	9632587410	8521479630	6541239870
2	prasad Phone: 9632587410 vishnu Mobile: 8521479630 reddy Mobile: 6541239870	9632587410	8521479630	6541239870
3	prasad Phone: 9632587410 vishnu Mobile: 8521479630 reddy Mobile: 6541239870 bobby Phone: 7413698521	9632587410	8521479630	6541239870	7413698521
Sheet1

Cell Formulas
Range	Formula
B1:F3	B1	=Phone($A1,COLUMNS($B:B))

Thankyou so much Peter

 

[Peter_SSs]

You're welcome.

If you wanted a vba solution, I'm wondering why you didn't ask that in post 1 instead of giving us a worksheet formula and asking us to modify that? Would have saved considerable time and effort.