How to Pull Certain Number from a Text String

Adroit

New Member
Joined
Jul 16, 2012
Messages
16
Hello,

I'm trying to pull a specific 10 digit number from a text string. The ID # is always 10 digits however sometimes there are dates in the data that need to be excluded as well as other numbers such as "I-9". The 10 digit number will always start with 1 or 8. Is there a macro or Formula to extract this data? Please find the example dataset below. Thanks in advance!


Edit: I should specify I want the "100118416" number from the first cell example or the "800011320" number from the 10th example. Thanks again.

NDM Abxx, Kexxxxx 100118416 2-29-12.pdf</SPAN>
VPG I9 Aberxxxxx, Dexxx 100113048 1-1-12.pdf</SPAN>
I-9 Abxxx, Rexx 100119966 4-5-12.pdf</SPAN>
NDM I9 Achxxxn, Paxx 100119008 3-15-2012.pdf</SPAN>
VPG I-9 Acxxxxx, Toxx 100112782 01-01-12.pdf</SPAN>
I-9 Acxxxr, Gxxx 100117854 2-27-12.pdf</SPAN>
NDM Acxxx, Angxxxxx 1001118015 3.3.12.pdf</SPAN>
ADXXX, DEXXX 100118892 I9.pdf</SPAN>
I-9 Adxxx, Chxxxxxxx 100119633 3-28-12.pdf</SPAN>
I-9 Adxxxx, Daxxx 800011320 6-14-12.pdf</SPAN>
I-9 Adxxxx,Jenxxxx 100121079 5 25 2012.pdf</SPAN>
VPG I9 Axxxx, Lxxxx E 100113621 1-1-12.pdf</SPAN>
NDM I9 Adaxx, Mxxx 100117676 2-16-2012.pdf</SPAN>
NDM I9 Adxxxx, Pxxx100117778 2-18-2012.pdf</SPAN>
ADXXX, PXXX T 100117778.pdf</SPAN>

<TBODY>
</TBODY>
 
Last edited:

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Whoops...apparently I can't count, the number I need to pull is always 9 digits! Thanks.
 
Upvote 0
Some of your numbers appear to be 9 digits. It looks like the date is always after the 10-digit number.

This array formula (confirmed with CTRL+SHIFT+ENTER) works for your posted sample data set:

=LEFT(MID(A1,MAX(MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$100),1)),0),MATCH(TRUE,(1*MID(A1,ROW($1:$100),1))<>9,0)),COUNT(1*MID(A1,ROW($1:$100),1))),10)
 
Upvote 0
Simpler formula (also array):

=MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$100),10)),0)+1,10)
 
Upvote 0
If you don't mind VB code, then you can use this UDF (user defined function)...
Code:
Function GetPattern(Source As String, ByVal Pattern As String) As String
  Dim X As Long, FindPattern As Long
  Do Until Left(Pattern, 1) <> "*"
    Pattern = Mid(Pattern, 2)
  Loop
  For X = 1 To Len(Source)
    If Mid(Source, X) Like Pattern & "*" Then
      FindPattern = X
      Exit For
    End If
  Next
  If FindPattern = 0 Then Exit Function
  For X = 1 To Len(Source) - FindPattern + 1
    If Mid(Source, FindPattern, X) Like Pattern Then
      GetPattern = Mid(Source, FindPattern, X)
      Exit For
    End If
  Next
End Function
which I posted in my mini-blog article here...

Find a text substring that matches a given "pattern"

See the article for the full method on how to use it, but for your particular case, after installing the UDF, you would use this formula on your worksheet...

=GetPattern(A2,"#########")


HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use NameOfTheUDF just like it was a built-in Excel function (as shown above).
 
Upvote 0

Excel 2010
AB
1NDM Abxx, Kexxxxx 100118416 2-29-12.pdf100118416
2VPG I9 Aberxxxxx, Dexxx 100113048 1-1-12.pdf100113048
3I-9 Abxxx, Rexx 100119966 4-5-12.pdf100119966
4NDM I9 Achxxxn, Paxx 100119008 3-15-2012.pdf100119008
5VPG I-9 Acxxxxx, Toxx 100112782 01-01-12.pdf100112782
6I-9 Acxxxr, Gxxx 100117854 2-27-12.pdf100117854
7NDM Acxxx, Angxxxxx 1001118015 3.3.12.pdf100111801
8ADXXX, DEXXX 100118892 I9.pdf100118892
9I-9 Adxxx, Chxxxxxxx 100119633 3-28-12.pdf100119633
10I-9 Adxxxx, Daxxx 800011320 6-14-12.pdf800011320
11I-9 Adxxxx,Jenxxxx 100121079 5 25 2012.pdf100121079
12VPG I9 Axxxx, Lxxxx E 100113621 1-1-12.pdf100113621
13NDM I9 Adaxx, Mxxx 100117676 2-16-2012.pdf100117676
14NDM I9 Adxxxx, Pxxx100117778 2-18-2012.pdf100117778
15ADXXX, PXXX T 100117778.pdf100117778
Sheet3
Cell Formulas
RangeFormula
B1{=MAX(IFERROR(MID($A1,ROW(INDIRECT("1:"&LEN(A1))),9)+0,""))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Simpler formula (also array):

=MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$100),10)),0)+1,10)

That will probably work given the data the OP has provided, but if a "number" like this...

1001199e6 (not the "e")

or like this...

10011.008 (note the decimal point)

were to be located before (or instead of) the actual 9-digit number, it would be found first.
 
Upvote 0
This is great information guys, thank you so much. Suppose I wanted to get the name out of the same set of data instead of the number, how would I go about doing that? Thanks again!
 
Upvote 0
This is great information guys, thank you so much. Suppose I wanted to get the name out of the same set of data instead of the number, how would I go about doing that? Thanks again!
Is "the name" the string after first "," and before the number?
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top