Formula to extract 10 or 11 digit phone numbers from random complex test string

[anmoldubey]

Hello Experts,

I a newbee in field of excel formula and need your help in a complex formula, where i need to extract phone numbers from a string of random text. This does not have a fix format for the string

Example set off strings:

Dring to data add9724516002
add 08107936777
8726169633 plz add. Me
9767488106 mujhe bhi add karo dosto

I have already tried many formulas but nothing seem to work. Formulas here dont seem to work perfectly -http://www.excelforum.com/excel-formulas-and-functions/1064845-extract-10-digit-number-only-from-text-string.html.

Anmol Dubey

[sandy666]

try:

Please Login or Register  to view this content.

with Control+Shift+Enter
it will extract ALL digits from the string.
If there will be problem with Scientific notation try this one:

Please Login or Register  to view this content.

with CSE also.

EDIT:
or (just for fun ) you can try with simply UDF function:
1. VBA code
2.

Please Login or Register  to view this content.

it will extract 10 or 11 digits only (no more, no less)

[FDibbins]

Or this regular formula...
=LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW($1:$10000)))) Courtesy of:*Ron Coderre

[samba_ravi]

Please Login or Register  to view this content.

try this formula, hope someone come with better formula