Extract Only Numbers From Text String
Page 1 of 15 12311 ... LastLast
Results 1 to 10 of 143

Thread: Extract Only Numbers From Text String

  1. #1
    Board Regular
    Join Date
    Dec 2005
    Location
    Seattle, WA
    Posts
    1,212
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Extract Only Numbers From Text String

    Dear Smartest Excelers Around,

    Is there a one cell formula that could take this string in cell A1:

    45t*&65/

    and extract only the numbers and deliver this

    4565

    to a single cell?

    The formula would have to be able to deal with all 255 ASCII characters and be copied down a column.
    Sincerely, Mike Girvin

  2. #2
    Board Regular schielrn's Avatar
    Join Date
    Apr 2007
    Location
    Cincinnati, Ohio
    Posts
    6,934
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract Only Numbers From Text String

    Always make a back up copy before trying new code, you never know what you might lose!


    - Posting guidelines, forum rules and terms of use

    -Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

    "The world suffers a lot. Not because of the violence of bad people, but because of the silence of good people!"

  3. #3
    MrExcel MVP Ron Coderre's Avatar
    Join Date
    Jan 2009
    Location
    Boston, Massachusetts
    Posts
    2,287
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract Only Numbers From Text String

    Try this:

    With
    A1 containing an alphanumeric string: eg 9128ABC37DEF465

    This ARRAY FORMULA, committed with CTRL+SHIFT+ENTER
    (instead of just ENTER) returns ONLY the numbers:
    Code:
    B1: =SUM(MID(A1,LARGE(ISNUMBER(--MID(A1,ROW($1:$25),1))*
    ROW($1:$25),ROW($A$1:INDEX($A:$A,COUNT(--MID(A1,ROW($1:$25),1))))),1)*
    10^(ROW(INDEX($1:$25,COUNT(--MID(A1,ROW(INDEX($1:$25,1,1):
    INDEX($1:$25,LEN(A1),1)),1)),1):INDEX($1:$25,1,1))-1))
    In the above example, the formula returns: 912837465

    That formula works for text up to 25 characters long.

    Is that something you can work with?
    Last edited by Ron Coderre; Jan 26th, 2010 at 04:10 PM. Reason: Typo...Chgd sumproduct to sum
    Best Regards,

    Ron Coderre
    Microsoft MVP-Excel (2006 - 2015)
    Using: Excel 2013 & 2016

  4. #4
    MrExcel MVP Ron Coderre's Avatar
    Join Date
    Jan 2009
    Location
    Boston, Massachusetts
    Posts
    2,287
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract Only Numbers From Text String

    I played around with the formula some more...
    This non-array formula seems to be working:
    Code:
     
    B1: =SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW($1:$25),1))*
    ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10)
    Best Regards,

    Ron Coderre
    Microsoft MVP-Excel (2006 - 2015)
    Using: Excel 2013 & 2016

  5. #5
    Board Regular
    Join Date
    Feb 2009
    Posts
    303
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract Only Numbers From Text String

    Ron, is there a way to modify the formula to get the 2nd number to the right instead of the sum? The result is 37.

  6. #6
    MrExcel MVP Ron Coderre's Avatar
    Join Date
    Jan 2009
    Location
    Boston, Massachusetts
    Posts
    2,287
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract Only Numbers From Text String

    Quote Originally Posted by pto160 View Post
    Ron, is there a way to modify the formula to get the 2nd number to the right instead of the sum? The result is 37.
    Apparently, my coffee has worn off...
    Can you give one or more examples of source text and
    the corresponding numbers to return?
    Best Regards,

    Ron Coderre
    Microsoft MVP-Excel (2006 - 2015)
    Using: Excel 2013 & 2016

  7. #7
    MrExcel MVP Ron Coderre's Avatar
    Join Date
    Jan 2009
    Location
    Boston, Massachusetts
    Posts
    2,287
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract Only Numbers From Text String

    Got it! I understand...evidently, the last coffee molecule just bumped into my brain.

    This formula returns the second sequence of numbers from a string in the format of: digits letters digits
    Code:
     
    B1: =LOOKUP(99^99,--("0"&MID(MID(A1,LEN(LOOKUP(10^99,--LEFT(A1,ROW($1:$25)
    )))+1,25),MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},MID(A1,LEN(LOOKUP(10^99,--LEFT(A1,
    ROW($1:$25))))+1,25)&"0123456789")),ROW($1:$25))))
    I'm hoping that can be shortened.

    That formula returns these results:
    For: 45t*&65/
    result: 65

    For: 9128A+BC37/E*465
    result: 37

    Does that help?
    Best Regards,

    Ron Coderre
    Microsoft MVP-Excel (2006 - 2015)
    Using: Excel 2013 & 2016

  8. #8
    Board Regular
    Join Date
    Dec 2005
    Location
    Seattle, WA
    Posts
    1,212
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Extract Only Numbers From Text String

    Schielrn,
    The formula from Domenic at post 6:
    http://www.mrexcel.com/forum/showthread.php?t=362184
    works great (I changed my string to cell A2):

    =SUM(MID(0&A2,LARGE(ISNUMBER(--MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))*ROW(INDIRECT("1:"&LEN(A2))),ROW(INDIRECT("1:"&LEN(A2))))+1,1)*10^ROW(INDIRECT("1:"&LEN(A2)))/10)

    Ron Coderre,
    This formula is beautiful (I changed my string to cell A2):

    =SUMPRODUCT(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$25),1))*
    ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10)

    Ron, I like your formula because of its compactness and non-array non-Ctrl-Shift-Enter.
    I like Domenic’s because of the robustness of the ROW(INDIRECT("1:"&LEN(A2))).
    Mashing your two formulas together (in Excel 2007 only – exceeds 7 nesting rule in Excel 2003) I got:

    =SUMPRODUCT(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW(INDIRECT("1:"&LEN($A$2))),1))*
    ROW(INDIRECT("1:"&LEN($A$2))),0),ROW(INDIRECT("1:"&LEN($A$2))))+1,1)*10^ROW(INDIRECT("1:"&LEN($A$2)))/10)

    I learn a lot from both formulas. In essence, are these the concepts of how the formula is working:

    1) Extract characters with MID
    2) Convert extracted characters to numbers and errors with --
    3) Ask array if elements are numbers with ISNUMBER
    4) Multiply array of TRUE/FALSE by position -- *ROW(INDIRECT("1:"&LEN($A$2)))
    5) Organize by position with LARGE (largest to smallest including zeroes)
    6) Add 1 to avoid error with MID caused by zeros from LARGE
    7) Concatenate a zero to beginning of string so the 1 added to the zeros will not cause error when MID extracts numbers
    8) Multiply the extracted numbers by 10^ROW(INDIRECT("1:"&LEN($A$2))) to get the correct number of zeros for each extracted number
    9) Divide by 10 to deal with the fact the we had a zero concatenated to the front of the string
    10) Added

    Am I getting the concepts, right?
    Sincerely, Mike Girvin

  9. #9
    Board Regular
    Join Date
    Feb 2009
    Posts
    303
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract Only Numbers From Text String

    It looks I should definitely consider upgrading to Excel 2007. Probably, exceeding 7 nested brackets. How would I make this work with Excel 2003? Can I separate it into two columns?

  10. #10
    MrExcel MVP Ron Coderre's Avatar
    Join Date
    Jan 2009
    Location
    Boston, Massachusetts
    Posts
    2,287
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract Only Numbers From Text String

    As much as I loathe giving array formula solutions
    (nobody I work with ever remembers to CSE them),
    I think there's a certain elegance to this one:
    Code:
     
    B1: =LOOKUP(10^99,--MID(A1,MIN(IF((--ISNUMBER(--MID(A1,ROW($1:$25),1))=0)*
    ISNUMBER(--MID(A1,ROW($2:$26),1)),ROW($2:$26))),ROW($1:$25)))
    Does that help?
    Best Regards,

    Ron Coderre
    Microsoft MVP-Excel (2006 - 2015)
    Using: Excel 2013 & 2016

Some videos you may like

User Tag List

Tags for this Thread

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
  •