Extract Only Numbers From Text String
Page 3 of 15 FirstFirst 1234513 ... LastLast
Results 21 to 30 of 143

Thread: Extract Only Numbers From Text String

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

    With
    A1: 91.28ABC37.1D2F465
    B1: 2

    This variation of the array formula I posted returns embedded numbers
    that can contain decimal values:
    Code:
     
    C1: =LOOKUP(10^99,--MID("|"&A1,SMALL(IF(((--ISNUMBER(--("0"&MID("|"&A1,
    ROW($1:$25),1)))=0)*ISNUMBER(--(MID("|"&A1,ROW($2:$26),1)))),ROW($2:$26)),B1),
    ROW($1:$25)))
    In the above example, the formula returns the second embedded number:
    37.1
    Best Regards,

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

  2. #22
    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

    This is going to work fantastic with embedded numbers, for example inventory parts. I will definitely use it for that.
    The first array formula works with non-embedded numbers.
    If the first number is 100.59, and you put in 1 for the position, it picks up 100.59, but if you put in 2, it picks up 59. The way around this is to go up by increments of two. But some numbers will be 464 and not 464.00, so the 3rd number will pick up the two digits after the decimal point.

    Sheet3

    A B C
    3 Formula Number of n
    4 464.59 DDFSDF 23.25 ccd 157.25 464.59 1
    5 464.68 DDFSDF 23.25 ccd 157.25 68.00 2
    6 464 DDFSDF 23.25 ccd 157.25 23.25 2
    7 464 DDFSDF 23.84 ccd 157.25 84.00 3

    Spreadsheet Formulas
    Cell Formula
    B4 {=LOOKUP(10^99,--MID("|"&A4,SMALL(IF(((--ISNUMBER(--MID("|"&A4,
    ROW($1:$1003),1)
    )
    =0)
    *ISNUMBER(--MID("|"&A4,ROW($2:$1004),1)))
    ,ROW($2:$1004))
    ,C4)
    ,
    ROW($1:$1003))
    )}
    Formula Array:
    Produce enclosing
    { } by entering
    formula with CTRL+SHIFT+ENTER!

    Maybe use another column to change numbers like 464 to 464.00 and then
    use the formula on the revised column?


    Excel tables to the web >>
    Excel Jeanie HTML 4

  3. #23
    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 think we finally have an all-purpose formula.

    With A1:A7 containing
    Code:
    45t*&65/
    9128A+BC37/E*465
    91a28ABC3712DEF465
    91.28ABC3712DEF465
    91.28ABC37.1D2F465
    464.59 DDFSDF 23.25 ccd 157.25
    123asdf.asdf.asdf456
     
    and
    C1: 2
    This array formula returns the specified number from the string,
    Code:
     
    B1: =LOOKUP(10^99,--MID("|"&A1,SMALL(IF(((--ISNUMBER(--("0"&MID(
    SUBSTITUTE(" "&A1," ","|"),ROW($1:$25),1)))=0)*ISNUMBER(--(MID(
    SUBSTITUTE(" "&A1," ","|"),ROW($2:$26),1)))),ROW($2:$26)),C1),ROW($1:$25)))
    Copy B1 and paste into B2:B7

    With the above examples, the formulas return these values:
    Code:
    45
    37
    28
    91.28
    2
    23.25
    456
    Note: If you want to display two decimal places, change the number format.

    Are we done, yet?
    Best Regards,

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

  4. #24
    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

    Yikes! I posted the wrong returned value list!
    Code:
     
    45 <-----Should be 65
    37
    28
    91.28 <--Should be 3712
    2 <------Should be 37.1
    23.25 
    456
    so the list of second embedded numbers should be this:
    Code:
    65
    37
    28
    3712
    37.1
    23.25
    Best Regards,

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

  5. #25
    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

    Yes, we are definitely done. This formula can pretty much work with any type of text format that is thrown at it. I thought regex was the only option for this, but I was proved wrong. Thanks so much for your help.

  6. #26
    New Member
    Join Date
    Mar 2012
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exclamation Re: Extract Only Numbers From Text String

    i just did what you said and pressing F9 i get the correct result but when i press control+shift+enter i get something that looks like 3,0697E+11 INSTEAD Of 306970490622...please help!!

  7. #27
    Legend VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Extract Only Numbers From Text String

    Just format the cell as Number and make the column wider.
    HTH, Peter
    Please test any code on a copy of your workbook.

  8. #28
    New Member
    Join Date
    Mar 2012
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract Only Numbers From Text String

    Quote Originally Posted by VoG View Post
    Just format the cell as Number and make the column wider.
    thank you it worked, sorry for that but i am newbie....

  9. #29
    New Member
    Join Date
    Jan 2013
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Extract only numbers but need decimals

    Quote Originally Posted by mgirvin View Post
    Schielrn,
    The formula from Domenic at post 6:
    Extracting Multiple Numbers from String
    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?

    I have found the above really useful and got it to work however have one big challenge I can't quite get my head around, what if the number is a decimal? Eg If the string is 2.28xbw I need it to extract 2.28 instead of 228 as these do, anyone any ideas?

  10. #30
    New Member
    Join Date
    May 2013
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Red face Re: Extract Only Numbers From Text String

    Quote Originally Posted by Ron Coderre View Post
    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)
    That worked for me! Thanks!!!

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
  •