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!

 

[T. Valko]

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!

Is the 10 digit number always the last set of digits in the string?

 

[legendary_popsicle]

Not necessarily. The email address could be after the 10-digit string also.

 

[shg]

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)

 

[legendary_popsicle]

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)

I appreciate the effort, but I'm trying to avoid using VBA/UDF. That being said, it doesn't seem to be working for me. I get a #VALUE! error.

 

[T. Valko]

I appreciate the effort, but I'm trying to avoid using VBA/UDF. That being said, it doesn't seem to be working for me. I get a #VALUE! error.

I don't think you'll be able to do this without VBA.

It works OK for me.

 

[legendary_popsicle]

I don't think you'll be able to do this without VBA.

It works OK for me.

Not sure what I'm doing differently, but I'm still getting a #VALUE!

 

[T. Valko]

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)

In testing this I find it returns the number string if it's longer than 10 digits:

A1 = Text 123 more text 123456789 123456789012

=GetTheNum(A1,10)

Returns: 123456789012

 

[legendary_popsicle]

In testing this I find it returns the number string if it's longer than 10 digits:

A1 = Text 123 more text 123456789 123456789012

=GetTheNum(A1,10)

Returns: 123456789012

Thank you! I was forgetting to add the ,10 in the formula. This works perfectly.

Challenge: Anyone else want to take a shot at a non-VBA-based solution?

 

[shg]

In testing this I find it returns the number string if it's longer than 10 digits:

A1 = Text 123 more text 123456789 123456789012

=GetTheNum(A1,10)

Returns: 123456789012

' returns the first match of nDigits or more digits

If you want that exact number, remove the comma in the pattern.