Page 1 of 3 123 LastLast
Results 1 to 10 of 24

How to Pull Certain Number from a Text String

This is a discussion on How to Pull Certain Number from a Text String within the Excel Questions forums, part of the Question Forums category; Hello, I'm trying to pull a specific 10 digit number from a text string. The ID # is always 10 ...

  1. #1
    New Member
    Join Date
    Jul 2012
    Posts
    12

    Default 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
    Last edited by Adroit; Jul 16th, 2012 at 04:37 PM.

  2. #2
    New Member
    Join Date
    Jul 2012
    Posts
    12

    Default 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. #3
    Board Regular
    The ONLY cool kid on the block
    BenMiller's Avatar
    Join Date
    Nov 2011
    Location
    Jerusalem
    Posts
    1,890

    Default 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)
    "I'm not a perfectionist; I'm just perfect."

  4. #4
    Board Regular
    The ONLY cool kid on the block
    BenMiller's Avatar
    Join Date
    Nov 2011
    Location
    Jerusalem
    Posts
    1,890

    Default Re: How to Pull Certain Number from a Text String

    Quote Originally Posted by Adroit View Post
    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
    "I'm not a perfectionist; I'm just perfect."

  5. #5
    Board Regular
    The ONLY cool kid on the block
    BenMiller's Avatar
    Join Date
    Nov 2011
    Location
    Jerusalem
    Posts
    1,890

    Default 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)
    "I'm not a perfectionist; I'm just perfect."

  6. #6
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    16,482

    Default 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).
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? Try one of these MrExcel HTML Maker, Excel jeanie or Borders-Copy-Paste

  7. #7
    MrExcel MVP Robert Mika's Avatar
    Join Date
    Jun 2009
    Location
    (current) UK, (origin) Poland
    Posts
    7,219

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



    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

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

  8. #8
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    16,482

    Default Re: How to Pull Certain Number from a Text String

    Quote Originally Posted by BenMiller View Post
    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.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? Try one of these MrExcel HTML Maker, Excel jeanie or Borders-Copy-Paste

  9. #9
    New Member
    Join Date
    Jul 2012
    Posts
    12

    Default 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. #10
    MrExcel MVP Robert Mika's Avatar
    Join Date
    Jun 2009
    Location
    (current) UK, (origin) Poland
    Posts
    7,219

    Default Re: How to Pull Certain Number from a Text String

    Quote Originally Posted by Adroit View Post
    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?
    - 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 .

Page 1 of 3 123 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com