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!

 

[pgc01]

Hi

I'm glad your problem is solved, but I read your oriiginal post:

Hey all,

I am working with data where I need to extract a 10-digit number from a string.

You asked for a 10-digit number, not commas or dots.

After reading your last posts I guess what you meant was "I need to extract a 10-character number from a string".

This is, of course, not relevant, what's important is that your problem is solved, it's just to clarify.

 

[legendary_popsicle]

Thanks for feedback! Glad you got it to work.

As promised i will try to explain how the formula works:


<TABLE style="WIDTH: 648pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=864 border=0><COLGROUP><COL style="WIDTH: 648pt; mso-width-source: userset; mso-width-alt: 31597" width=864><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 648pt; BORDER-BOTTOM: #f0f0f0; HEIGHT: 30pt; BACKGROUND-COLOR: transparent" width=864 height=40 rowSpan=2>=LOOKUP(9.99999999999999E+307,--MID(SUBSTITUTE(A1&"x"," ","x"),ROW(INDIRECT("1:"&LEN(A1))),10))



</TD></TR><TR style="HEIGHT: 15pt" height=20></TR><TR style="HEIGHT: 15pt" height=20><TD id=td_post_2918277 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20></TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>The red part will substitute all spaces with an x and put an x at the end of the string. This to isolate the string.</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>The green part will create an array of numbers {1,2,3… used to indicate the position where to look for the string</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>The blue part will extract an array of 10 digit strings, from the string with starting point based on the array above I.e. Char {1-10, 2-11,3-12...</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Doing a Lookup with this very big number on this array of strings will return the last (rightmost) 10 digit string which is 100% numbers</TD></TR></TBODY></TABLE>


Hope this makes sense.

Thank you very very much. This has been extremely helpful.

 

[abhijeetparakh]

Function GetTheNum(sInp As String, nDigits As Long) As String
    ' returns the first match of nDigits or more digits
 
    With CreateObject("vbscript.regexp")
        .Pattern = "\d{" & nDigits & ",}"
        If .Test(sInp) Then
            GetTheNum = .Execute(sInp)(0).Value
        End If
    End With
End Function

E.g., =GetTheNum(A1, 10)

this worked perfectly ... thank you