Page 2 of 15 FirstFirst 123412 ... LastLast
Results 11 to 20 of 143

Thread: Extract Only Numbers From Text String

  1. #11
    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

    Quote Originally Posted by pto160 View Post
    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?
    Yes you can separate into separate columns is what most people do (or modify your formula to not need 7 ifs) or you can put them within named ranges.
    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!"

  2. #12
    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. Thanks. The formula you gave me is totally robust and dynamic. It picks up the 2nd number no matter the text surrounding it, even including no spaces between the text and the number?

    If I wanted to get the nth number or the third number, does this require a total rewrite of the formula?

  3. #13
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,855
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Extract Only Numbers From Text String

    9) Divide by 10 to deal with the fact the we had a zero concatenated to the front of the string
    As you've already indicated, the numbers extracted by the first MID function are multiplied by 10^ROW(INDIRECT("1:"&LEN($A$2))) to get the correct number of zeros for the extracted numbers.

    Note that the first extracted number is multiplied by the first number in the array 10^ROW(INDIRECT(...)), which is 10^1 or 10. Therefore, if the first number extracted by MID is 4, then 40 would be returned instead of 4. The second extracted number would then be multiplied by 10^2, the third by 10^3, and so on. Hence, we divide by 10 at the end prior to summing.

    However, we could avoid dividing by 10 if we use the following instead...

    10^(ROW(INDIRECT("1:"&LEN(A2)))-1)

    Now, the first multiplier is 10^0 or 1 instead of 10^1 or 10. So if the first number extracted by MID is 4, then 4 would be returned, not 40. Then there would be no need to divide by 10.

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

    Default Re: Extract Only Numbers From Text String

    With:
    A1: 91a28ABC3712DEF465

    This array formula that pulls whichever sequential number you specify:
    C1: (the ordinal of the embedded number to pull....eg 2)
    Code:
     
     
    B1: =LOOKUP(10^99,--MID("|"&A1,SMALL(IF(((--ISNUMBER(--MID("|"&A1,
    ROW($1:$25),1))=0)*ISNUMBER(--MID("|"&A1,ROW($2:$26),1))),ROW($2:$26)),C1),
    ROW($1:$25)))
    In the above example, the formula returns the 2nd number in that string:
    28

    If you change C1 to 3...the formula returns: 3712

    Does that help?
    Best Regards,

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

  5. #15
    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, yes it works great. Is the maximum numbers of characters 25 from the 1st number?

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

    Default Re: Extract Only Numbers From Text String

    Thank you very much, Domenic and Ron!!!
    Sincerely, Mike Girvin

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

    Default Re: Extract Only Numbers From Text String

    Quote Originally Posted by pto160 View Post
    Ron, yes it works great. Is the maximum numbers of characters 25 from the 1st number?
    No, 25 is not an upper limit. Sometimes these kinds of formulas are used in
    tens of thousands of cells. I just used 25 characters to keep the formula
    overhead down. Change the 25's to whatever makes sense for your
    situation. Remember to change the 26's to one number higher than your new limit.
    Best Regards,

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

  8. #18
    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

    Thanks Ron. I played around with it and I got it go out 7 numbers. I cannot imagine going beyond that with the dataset that I have. The formula picks up the number no matter the format of the string. Just wondering,

    This formula does pick up decimal points, but it treats the number characters before and after the decimal point as 2 separate numbers, so you have to go up in increments of 2 in C1 to pick up the next number.

    Excel Jeanie HTMLSheet3

    A B C
    3 4884.23 464.25 1111500.78 464.25 3
    4 4884.23 464.25 1111500.78 25.00 4
    5 4884.23 464.25 1111500.78 1111500.78 5

    Is this the best way to do it?

    I can do this in a separate column like D1. Is there a way to go one character to the left of the result in B1. This will pick up any negative sign, which I can then concatenate with B1.
    Great formula.

    Excel tables to the web >> Excel Jeanie HTML 4

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

    Default Re: Extract Only Numbers From Text String

    It would be so disheartening to learn that your actual cells contained numbers separated by spaces. I hope that is not the case because the formula to pluck out those numbers is so much simpler:

    With
    A1: 4884.23 464.25 1111500.78
    B1: (the segment to return....eg 2)

    This formula returns that value:
    Code:
     
    C1: =LOOKUP(10^99,--LEFT(MID(A1,FIND("|",SUBSTITUTE(" "&A1," ","|",B1)),255),
    ROW($1:$15)))
    In the above example, the formula returns: 464.25
    Best Regards,

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

  10. #20
    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 works great. I will probably use the other formula more because it is more robust. It works with text, gives me the nth number (no separate formula for last and first number and numbers beside text with no spaces.

    Is there a way to change a number from 0 to 2 decimal points?
    Usually with some ERP systems, numbers like 464.00 that are exported into Excel are converted to 464.

    Excel Jeanie HTMLSheet3

    A B
    7 Result
    8 CC 464 C 1111500.78 hghghgh 784.24 CC 464.00 C 1111500.78 hghghgh 784.24

    This means it will always go up by increments of two in C1.
    Let me know if this should be a separate thread.
    Excel tables to the web >> Excel Jeanie HTML 4

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
  •