Whoops...apparently I can't count, the number I need to pull is always 9 digits! Thanks.
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 VPG I9 Aberxxxxx, Dexxx 100113048 1-1-12.pdf I-9 Abxxx, Rexx 100119966 4-5-12.pdf NDM I9 Achxxxn, Paxx 100119008 3-15-2012.pdf VPG I-9 Acxxxxx, Toxx 100112782 01-01-12.pdf I-9 Acxxxr, Gxxx 100117854 2-27-12.pdf NDM Acxxx, Angxxxxx 1001118015 3.3.12.pdf ADXXX, DEXXX 100118892 I9.pdf I-9 Adxxx, Chxxxxxxx 100119633 3-28-12.pdf I-9 Adxxxx, Daxxx 800011320 6-14-12.pdf I-9 Adxxxx,Jenxxxx 100121079 5 25 2012.pdf VPG I9 Axxxx, Lxxxx E 100113621 1-1-12.pdf NDM I9 Adaxx, Mxxx 100117676 2-16-2012.pdf NDM I9 Adxxxx, Pxxx100117778 2-18-2012.pdf ADXXX, PXXX T 100117778.pdf
Last edited by Adroit; Jul 16th, 2012 at 04:37 PM.
Whoops...apparently I can't count, the number I need to pull is always 9 digits! Thanks.
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)
"I'm not a perfectionist; I'm just perfect."
Simpler formula (also array):
=MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$100),10)),0)+1,10)
"I'm not a perfectionist; I'm just perfect."
If you don't mind VB code, then you can use this UDF (user defined function)...
which I posted in my mini-blog article here...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
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).
Rick's "mini" blog... http://www.excelfox.com/forum/f22/
.
Want to post a small screen shot? See here.
Excel 2010
A B 1 NDM Abxx, Kexxxxx 100118416 2-29-12.pdf 100118416 2 VPG I9 Aberxxxxx, Dexxx 100113048 1-1-12.pdf 100113048 3 I-9 Abxxx, Rexx 100119966 4-5-12.pdf 100119966 4 NDM I9 Achxxxn, Paxx 100119008 3-15-2012.pdf 100119008 5 VPG I-9 Acxxxxx, Toxx 100112782 01-01-12.pdf 100112782 6 I-9 Acxxxr, Gxxx 100117854 2-27-12.pdf 100117854 7 NDM Acxxx, Angxxxxx 1001118015 3.3.12.pdf 100111801 8 ADXXX, DEXXX 100118892 I9.pdf 100118892 9 I-9 Adxxx, Chxxxxxxx 100119633 3-28-12.pdf 100119633 10 I-9 Adxxxx, Daxxx 800011320 6-14-12.pdf 800011320 11 I-9 Adxxxx,Jenxxxx 100121079 5 25 2012.pdf 100121079 12 VPG I9 Axxxx, Lxxxx E 100113621 1-1-12.pdf 100113621 13 NDM I9 Adaxx, Mxxx 100117676 2-16-2012.pdf 100117676 14 NDM I9 Adxxxx, Pxxx100117778 2-18-2012.pdf 100117778 15 ADXXX, PXXX T 100117778.pdf 100117778 Sheet3
Array Formulas Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Cell Formula B1 {=MAX(IFERROR(MID($A1,ROW(INDIRECT("1:"&LEN(A1))),9)+0,""))}
Note: Do not try and enter the {} manually yourself
- Read the Posting Guidelines, Forum Rules & FAQs
- Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste
- If posting vba code, please use Code Tags .
Rick's "mini" blog... http://www.excelfox.com/forum/f22/
.
Want to post a small screen shot? See here.
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!
- Read the Posting Guidelines, Forum Rules & FAQs
- Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste
- If posting vba code, please use Code Tags .
Like this thread? Share it with others