Page 4 of 15 FirstFirst ... 2345614 ... LastLast
Results 31 to 40 of 143

Thread: Extract Only Numbers From Text String

  1. #31
    New Member
    Join Date
    Mar 2014
    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 Ron Coderre View Post
    I think we finally have an all-purpose formula.
    Greetings Ron,

    I found this information quite useful solving an issue I had. However, I have run into som problems.
    I am using the formula below extracting numbers from a string formated something like this: NUMBER-NUMBER TEXT TEXT (TEXT) NUMBER
    However, when the first bracket of texts first three letters resembles that of a month (example given: MARCONIAN) the formual returns a date in number value (41712) and not the actualy two-digit number.

    I expect the formual need to change format somehow, but I fail to locate how/where.
    I have tried altering format in both the source and the target cell.

    Formula: =HVISFEIL(SLŇ.OPP(10^99;--DELTEKST("|"&J15;N.MINST(HVIS(((--ERTALL(--("0"&DELTEKST(BYTT.UT(" "&J15;" ";"|");RAD($1:$60);1)))=0)*ERTALL(--(DELTEKST(BYTT.UT(" "&J15;" ";"|");RAD($2:$61);1))));RAD($2:$61));2);RAD($1:$60)));" ")

    (Formula is translated to localized language, the original formula below)

    =IFERROR(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))),””)

  2. #32
    Board Regular
    Join Date
    May 2013
    Posts
    1,634
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Extract Only Numbers From Text String

    Are all strings to process similar to the string you provided, for example: "12-345 this is (text) 56"? If so, you do not need to use that general formula to solve the problem.

  3. #33
    New Member
    Join Date
    Mar 2014
    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 IstvŠn Hirsch View Post
    Are all strings to process similar to the string you provided, for example: "12-345 this is (text) 56"? If so, you do not need to use that general formula to solve the problem.
    No, they are not. Due to sloppy input there are spaces (" ") in random locations on a number of the entries.

    We have looked into various solutions for this, but the amount of data is vast, and thus far this is the only formula that extracts the numbers with a relative precision.

    The only information I am interessted in is recorded like this (where X, Y and Z being number values) XXX-YY ZZZZ. The entry can contain any number of spaces and or characters anywhere in the cell, however it will never split the number values.

    Alternatively I could return all 3-digit numbers in first column, then return all 2-digit numbers in second and last all 4-digit numbers in last column, but I would not know how to accomplish this.

  4. #34
    New Member
    Join Date
    Mar 2014
    Location
    Brazil
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up Re: Extract Only Numbers From Text String

    Quote Originally Posted by Ron Coderre View Post
    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?
    This formula worked like a charm for me, it made me create an account on the forum just to thank you guys who created it!

    Excel 2013 Win7

  5. #35
    New Member
    Join Date
    Apr 2014
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract Only Numbers From Text String

    Will this formula work for an alphanumeric string longer than 14 characters? If not, are you aware of any function that will provide only the numerical values from the following string?

    2297244910LA172/165


    I'd like for the function to return a value of 2297244910172165 in the example above.


    Tks. dhaile

    =========================================

    Quote Originally Posted by Ron Coderre View Post
    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?

  6. #36
    Board Regular
    Join Date
    Jun 2011
    Location
    Mumbai, Maharashtra,India.
    Posts
    1,092
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract Only Numbers From Text String

    Hello Experts,
    With regarding to this post i have very simple query in front if you, hope will get great help.
    very simple--
    abc123
    123abc
    a123b
    12bnm12
    a12bn
    Looking for only numbers in next cell. Will this possible. if yes, please post the help.




    [QUOTE=Ron Coderre;2193647]Try this:

  7. #37
    Board Regular yesterdays's Avatar
    Join Date
    Oct 2014
    Location
    VietNam
    Posts
    337
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract Only Numbers From Text String

    Use this :
    =SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))*ROW(INDIRECT("1:"&LEN(A1))),0,1),ROW(INDIRECT("1:"&LEN(A1))))+1,1)*10^(ROW(INDIRECT("1:"&LEN(A1)))-1))

  8. #38
    Board Regular
    Join Date
    Jun 2011
    Location
    Mumbai, Maharashtra,India.
    Posts
    1,092
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract Only Numbers From Text String

    Excellent... Appriciate your brain power

    Quote Originally Posted by yesterdays View Post
    Use this :
    =SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))*ROW(INDIRECT("1:"&LEN(A1))),0,1),ROW(INDIRECT("1:"&LEN(A1))))+1,1)*10^(ROW(INDIRECT("1:"&LEN(A1)))-1))

  9. #39
    New Member
    Join Date
    Dec 2014
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract Only Numbers From Text String

    Hi, I have searched this thread but so far none of the formulas are working. Perhaps I am to new to excel and have an error in cut/paste? Anyways I am looking for a formula to extract only the numbers. So for example a part number BROTHER HL-2270DW and apply formula so output is only "2270". Any suggestion?

  10. #40
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,252
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Extract Only Numbers From Text String

    Quote Originally Posted by Brusky View Post
    Hi, I have searched this thread but so far none of the formulas are working. Perhaps I am to new to excel and have an error in cut/paste? Anyways I am looking for a formula to extract only the numbers. So for example a part number BROTHER HL-2270DW and apply formula so output is only "2270". Any suggestion?
    The formula posted in Message #37 works for me.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

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
  •