How to Pull Certain Number from a Text String

[Adroit]

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>

 

[Adroit]

There was supposed to be a space between the double quotes in the above formula...

=TRIM(GetPattern(SUBSTITUTE(A16,"."," "),"#########[ 0-9]"))

Rick this is working great. The issue I'm having extracting numbers with ".pdf" on the end. See below for examples:

Axxx, XXXX T 100117778.pdf</SPAN>

Adxxx, Saxxxxx 100116851.pdf</SPAN>

Agxxxxx, Virxxxxx, 100116830.pdf</SPAN>

<TBODY>
</TBODY><COLGROUP><COL></COLGROUP>

 

[Rick Rothstein]

Rick this is working great. The issue I'm having extracting numbers with ".pdf" on the end. See below for examples:

Axxx, XXXX T 100117778.pdf

Adxxx, Saxxxxx 100116851.pdf

Agxxxxx, Virxxxxx, 100116830.pdf

<tbody>
</tbody>

What problem are you having? The formula works fine for me for those text strings (provided you change the A16 reference to point to a cell containing the text).

 

[Adroit]

What problem are you having? The formula works fine for me for those text strings (provided you change the A16 reference to point to a cell containing the text).

I changed the reference to the correct location. I have over 4,400 entries and it's capturing all of them exempt for those such as the example above. I get a "Blank" return for values with .pdf at the end. There are 982 of these entries Blank returns. Maybe I'm missing something?

<TBODY>
</TBODY><COLGROUP><COL></COLGROUP>

 

[Rick Rothstein]

I changed the reference to the correct location. I have over 4,400 entries and it's capturing all of them exempt for those such as the example above. I get a "Blank" return for values with .pdf at the end. There are 982 of these entries Blank returns. Maybe I'm missing something?

I think I know what might be going on... I think you are using the first formula I posted which was missing a character and missed my follow-up message where I corrected the problem (on top of which, you posted the correct formula in your question even though I think you are not using it). Replace the formula you have with this one (changing the cell reference as needed) and I think your problem will go away...

=TRIM(GetPattern(SUBSTITUTE(A2,"."," "),"#########[ 0-9]"))

The missing character was a space located in the third argument for the SUBSTITUTE function.