Page 13 of 15 FirstFirst ... 31112131415 LastLast
Results 121 to 130 of 143

Thread: Extract Only Numbers From Text String

  1. #121
    New Member
    Join Date
    Mar 2014
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract Only Numbers From Text String

    Hello Ron, what about this short formula which I learned from Mike Girvin? It worksperfectly: =--(TEXTJOIN("",,IFERROR(--(MID(A6,ROW(INDIRECT("1:"&LEN(A6))),1)),"")))

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

    Default Re: Extract Only Numbers From Text String

    Quote Originally Posted by dalbega View Post
    Hello Ron, what about this short formula which I learned from Mike Girvin? It worksperfectly: =--(TEXTJOIN("",,IFERROR(--(MID(A6,ROW(INDIRECT("1:"&LEN(A6))),1)),"")))
    Unless something has changed with the non-subscription version of Excel 2016 (I don't have it, so I don't know), according to Microsoft, TEXTJOIN is available if you are an Office 365 subscriber and have the latest version of Office installed. It's also available with Excel Online, Excel Mobile, Excel for iOS, and Excel for Android phones and tablets. So at the moment, that formula is not usable by the majority of Excel users.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  3. #123
    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

    Quote Originally Posted by dalbega View Post
    Hello Ron, what about this short formula which I learned from Mike Girvin? It worksperfectly: =--(TEXTJOIN("",,IFERROR(--(MID(A6,ROW(INDIRECT("1:"&LEN(A6))),1)),"")))
    And, when it comes to Excel 2016, there is a simpler formula:

    =regexreplace(A2;"[^0-9]";"")

  4. #124
    Board Regular
    Join Date
    Sep 2008
    Location
    Melbourne, Australia
    Posts
    1,391
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract Only Numbers From Text String

    The following formula worked perfect for me until I got to a set of values that gave me strange answers.
    Quote Originally Posted by Rick Rothstein View Post

    =-LOOKUP(0,-LEFT(MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),300),ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1)))))
    My data has values like
    36.PX, 38.PAX, 40 PAX, 9 PAX, 42.px, 88 PAX and so on. There is no consistency. Still the above formula was working okay. But for some reason 9 PAX is giving me the answer 0.875 where as it should be simple 9.

    Anything I can do to fix this problem?

    Thanks for your time more for your brain power.

    Asad

  5. #125
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    11,086
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Extract Only Numbers From Text String

    Try the below as it seems to work for your examples posted...

    =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)
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  6. #126
    Board Regular
    Join Date
    Sep 2008
    Location
    Melbourne, Australia
    Posts
    1,391
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract Only Numbers From Text String

    Quote Originally Posted by MARK858 View Post
    Try the below as it seems to work for your examples posted...
    Brilliant.
    Works perfect.
    Thanks a lot.
    Asad

  7. #127
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Extract Only Numbers From Text String

    Quote Originally Posted by asad View Post
    Works perfect.
    Just beware - if you subsequently insert any rows at the top of the sheet, the formula will return an incorrect result.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

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

    Quote Originally Posted by asad View Post
    The following formula worked perfect for me until I got to a set of values that gave me strange answers.


    My data has values like
    36.PX, 38.PAX, 40 PAX, 9 PAX, 42.px, 88 PAX and so on. There is no consistency. Still the above formula was working okay. But for some reason 9 PAX is giving me the answer 0.875 where as it should be simple 9.

    Anything I can do to fix this problem?

    Thanks for your time more for your brain power.

    Asad
    The problem, as usual, is caused by the tendency of excel to convert everything to date or time value. In this case the problem arises when the first digit in the cell is followed by a space and a "p" or "a". Excel interpretes this situation as 9 pm or 9 am and converts them to time. So the solution (in the framework of the formula above) is avoiding this structure to occur.

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

    Default Re: Extract Only Numbers From Text String

    Quote Originally Posted by asad View Post
    The following formula worked perfect for me until I got to a set of values that gave me strange answers.

    =-LOOKUP(0,-LEFT(MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),300),ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1)))))
    My data has values like
    36.PX, 38.PAX, 40 PAX, 9 PAX, 42.px, 88 PAX and so on. There is no consistency. Still the above formula was working okay. But for some reason 9 PAX is giving me the answer 0.875 where as it should be simple 9.

    Anything I can do to fix this problem?
    Quote Originally Posted by István Hirsch View Post
    The problem, as usual, is caused by the tendency of excel to convert everything to date or time value. In this case the problem arises when the first digit in the cell is followed by a space and a "p" or "a". Excel interpretes this situation as 9 pm or 9 am and converts them to time. So the solution (in the framework of the formula above) is avoiding this structure to occur.
    Give this a try...

    =-LOOKUP(0,-LEFT(MID(SUBSTITUTE(SUBSTITUTE(A1,"P","X"),"A","X"),MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),300),ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1)))))
    Last edited by Rick Rothstein; Feb 7th, 2017 at 05:36 AM.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  10. #130
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Extract Only Numbers From Text String

    Quote Originally Posted by Rick Rothstein View Post
    Give this a try...

    =-LOOKUP(0,-LEFT(MID(SUBSTITUTE(SUBSTITUTE(A1,"P","X"),"A","X"),MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),300),ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1)))))
    For the limited examples this poster had that may work, but as a general extractor it is not reliable.
    eg "9DECT"
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

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
  •