How to Pull Certain Number from a Text String

Thanks:  0
Likes:  0

# Thread: How to Pull Certain Number from a Text String

1. ## How to Pull Certain Number from a Text String

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

2. ## Re: How to Pull Certain Number from a Text String

Whoops...apparently I can't count, the number I need to pull is always 9 digits! Thanks.

3. ## Re: How to Pull Certain Number from a Text String

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)

4. ## Re: How to Pull Certain Number from a Text String

Whoops...apparently I can't count, the number I need to pull is always 9 digits! Thanks.
Not in your seventh example - NDM Acxxx, Angxxxxx 1001118015 3.3.12.pdf

5. ## Re: How to Pull Certain Number from a Text String

Simpler formula (also array):

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

6. ## Re: How to Pull Certain Number from a Text String

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).

7. ## Re: How to Pull Certain Number from a Text String

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

Sheet3

Array Formulas
CellFormula
B1{=MAX(IFERROR(MID(\$A1,ROW(INDIRECT("1:"&LEN(A1))),9)+0,""))}
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

8. ## Re: How to Pull Certain Number from a Text String

Originally Posted by BenMiller
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.

9. ## Re: How to Pull Certain Number from a Text String

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!

10. ## Re: How to Pull Certain Number from a Text String

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?

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•