extract 10 digit number only from text string

Hi Friends

I want to try extract 10 digit mobile number from text sting
any formula to do it

text is like

Mr. Pradeep M. Pardeshi,Plot No.5, Gadkari Nagar,Opp. Ganesh Traders, Khadka Road,Bhusawal, Jalgaon - 425201-9021286428-pardeshi.pradeep007@gmail.com

output :- 9021286428

any help much appreciate

is this section always in this format?

- 425201-9021286428-

dash space 6 digits dash ten digits dash ?

Formula:

Please Login or Register  to view this content.

This will extract the highest 10-digit number it can find in the cell...
In B2 - Array entered (press Ctrl+Shift+Enter):

=MAX(IFERROR(--MID(A2,ROW(INDEX($A:$A,1):INDEX($A:$A,LEN($A2)-9)),10),0))

Copy B2 downward as far as required.

Regular formula

in B2 and copy down

=TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",255)),225)),":-",""),"-",REPT(" ",255)),255))

Shorter a bit:

=MAX(IFERROR(INDEX(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),10)+0,),""))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

Originally Posted by stunn

=MAX(IFERROR(--MID(A2,ROW(INDEX($A:$A,1):INDEX($A:$A,LEN($A2)-9)),10),0))

Originally Posted by bebo021999

=MAX(IFERROR(INDEX(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),10)+0,),""))

Of course, it should probably be mentioned that both of these solutions depend on there being no other numbers within the string which are greater than 10 digits long.

In general, if this cannot be guaranteed, then you need something like:

=MID(A2,MATCH(12,MMULT(ABS(ISNUMBER(0+MID(MID("ζ"&A2&"ζ",ROW(INDIRECT("1:"&LEN(A2)-10)),12),{1,2,3,4,5,6,7,8,9,10,11,12},1))-{1,0,0,0,0,0,0,0,0,0,0,1}),{1;1;1;1;1;1;1;1;1;1;1;1}),0),10)

which, incidentally, does not require CSE, and would return correctly:

1234123411

from a string such as:

ABC 1234123411 DEF 123412341234

whereas the two previous set-ups would both incorrectly give:

3412341234

Regards

A little bit shorter version of the one in my previous post.

=LEFT(TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A2,":-"," ")," ",REPT(" ",255)),225)),10)

bebo021999 & XOR LX,

I tend to avoid INDIRECT because of its volatile nature, and there was nothing in the spreadsheet to suggest that there would ever be numbers longer than 10 digits in the cells to be examined.

You're welcome and thank you for the feedback!