# Find and return 10-digit number from string

#### legendary_popsicle

##### New Member
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:

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!

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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:
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?

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

Code:
``````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)

Code:
``````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.

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.

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!

Code:
``````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

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?

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.

Replies
14
Views
710
Replies
7
Views
361
Replies
23
Views
1K
Replies
6
Views
171
Replies
3
Views
219

### Forum statistics

1,203,052
Messages
6,053,223
Members
444,648
Latest member
sinkuan85 ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back