Find and return 10-digit number from string

[legendary_popsicle]

Hey all,

I am working with data where I need to extract a 10-digit number from a string. Where the 10-digit number is in the string varies, so I can't use a simple left/len combination. Here's an example below:

something and email@me.com 1234567890 stuff things
​

The formula I am using now is this:

=MID(F4,MIN(FIND({0,1,2,3,4,5,6,7,8,9},F4&"0123456789")),10)
​

This finds the first number in a string and returns that number and the next 9 digits for a total of a 10-digit string (so it would return 1234567890). However, I run into an issue when I have a string that has numbers in the email address.

So this:

number in email123@me.com 1234567890 example
​

Would return this:

123@me.com
​

Is there a way to modify the formula above so that it searches for 10 consecutive numbers and not just the first number in a string?

Thank you all!

 

[fairwinds]

Hi,

Try:

=LOOKUP(9.99999999999999E+307,--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),10))

 

[shg]

Nice and simple, fairwinds.

 

[legendary_popsicle]

Hi,

Try:

=LOOKUP(9.99999999999999E+307,--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),10))

Looks like it works fine except when there is a number after the 10-digit number:

something email23@me.com 1234567890 here1
​

The string above returns a 1.

 

[fairwinds]

Then I guess you have blanks in the end. Does this work better?

=LOOKUP(9.99999999999999E+307,--MID(SUBSTITUTE(A1," ",""),ROW(INDIRECT("1:"&LEN(A1))),10))

 

[legendary_popsicle]

I came up with a messy solution, but it works for now:

=IF(ISERROR(TRIM(MID(F4,MIN(FIND({0,1,2,3,4,5,6,7,8,9},F4&"0123456789")),10)*1)),TRIM(MID(SUBSTITUTE(F4,TRIM(MID(F4,MIN(FIND({0,1,2,3,4,5,6,7,8,9},F4&"0123456789")),10)),""),MIN(FIND({0,1,2,3,4,5,6,7,8,9},SUBSTITUTE(F4,TRIM(MID(F4,MIN(FIND({0,1,2,3,4,5,6,7,8,9},F4&"0123456789")),10)),"")&"0123456789")),10)),TRIM(MID(F4,MIN(FIND({0,1,2,3,4,5,6,7,8,9},F4&"0123456789")),10)*1))*1
​

Basically, if it returns a substring with non-numeric characters, then it removes that from the original string and looks again. Kinda messy though... is there a better way to write this?

​

 

[legendary_popsicle]

Then I guess you have blanks in the end. Does this work better?

=LOOKUP(9.99999999999999E+307,--MID(SUBSTITUTE(A1," ",""),ROW(INDIRECT("1:"&LEN(A1))),10))

That still doesn't work if there are numbers after the 10-digit numeric string.

 

[Rick Rothstein]

Is a VB solution acceptable? If so, here is a UDF (user defined function) that you can use...

Function TenDigits(S As String) As Long
  Dim X As Long
  For X = 1 To Len(S)
    If Mid(S, X, 10) Like "##########" Then
      TenDigits = Mid(S, X, 10)
      Exit For
    End If
  Next
End Function

If you are unfamiliar with UDFs, you can install the above by pressing ALT+F11 from any worksheet to go into the VB editor and, once there, click Insert/Module from it menu bar, then copy/paste the above code into the code window that opened up. That's it... go back to your worksheet and use TenDigits like you would any other Excel function. For example...

=TenDitgits(A1)

 

[fairwinds]

Not giving up yet

=LOOKUP(9.99999999999999E+307,--MID(SUBSTITUTE(A1&"x"," ",""),ROW(INDIRECT("1:"&LEN(A1))),10))

(I'm a bit rusty after a fiew years almost without Excel and MrExcel)

 

[legendary_popsicle]

Is a VB solution acceptable? If so, here is a UDF (user defined function) that you can use...

Function TenDigits(S As String) As Long
  Dim X As Long
  For X = 1 To Len(S)
    If Mid(S, X, 10) Like "##########" Then
      TenDigits = Mid(S, X, 10)
      Exit For
    End If
  Next
End Function

If you are unfamiliar with UDFs, you can install the above by pressing ALT+F11 from any worksheet to go into the VB editor and, once there, click Insert/Module from it menu bar, then copy/paste the above code into the code window that opened up. That's it... go back to your worksheet and use TenDigits like you would any other Excel function. For example...

=TenDitgits(A1)

Hey Rick,

Check the first page... Someone else found a working UDF solution. I wanted to find an all-inclusive formula solution.

Not giving up yet

=LOOKUP(9.99999999999999E+307,--MID(SUBSTITUTE(A1&"x"," ",""),ROW(INDIRECT("1:"&LEN(A1))),10))

(I'm a bit rusty after a fiew years almost without Excel and MrExcel)

I still have one more example that is not working:

Text - words - 7000028827 3dvision - abc31@me.net

That returns 288273
​

 

[Rick Rothstein]

Only you know your data, but as long as there are no other 10 (or longer) digit numbers in your text, you can use this array-entered** formula to retrieve the 10-digit number...

=MAX(IF(ISNUMBER(--MID(A2,ROW(INDIRECT("1:"&LEN(A2))),10)),--MID(A2,ROW(INDIRECT("1:"&LEN(A2))),10)))

**Commit this formula using CTRL+SHIFT+ENTER, not just Enter by itself