Page 5 of 15 FirstFirst ... 34567 ... LastLast
Results 41 to 50 of 143

Thread: Extract Only Numbers From Text String

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

    ok - I just try #37 and was able to make it work. Probably input error that it did not work the first time. Thank you for the suggestion.

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

    The only problem with that Formula in #37 is it will return a number, so any leading zero will be by pass

  3. #43
    New Member
    Join Date
    Jan 2015
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract Only Numbers From Text String

    Hi, i got a question here regarding for extracting the only number. But how about fraction? I would like to extract this.

    E.g. Flamengo (2/13) to only 2/13

    Any idea?

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

    Default Re: Extract Only Numbers From Text String

    Quote Originally Posted by oddchecker View Post
    Hi, i got a question here regarding for extracting the only number. But how about fraction? I would like to extract this.

    E.g. Flamengo (2/13) to only 2/13

    Any idea?
    Unfortunately one example is usually not enough to decide on a course of action...

    Is the number always at the end of the text as shown in your example? If not, can other numbers (digits) be located after it?

    Is the number always surrounded by parentheses? If so, will those parentheses always be the only parentheses in the text?

    Can there be any other numbers (digits) located before the number you want?
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  5. #45
    Board Regular
    Join Date
    Jul 2011
    Location
    Manhattan
    Posts
    740
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract Only Numbers From Text String

    Quote Originally Posted by oddchecker View Post
    Hi, i got a question here regarding for extracting the only number. But how about fraction? I would like to extract this.

    E.g. Flamengo (2/13) to only 2/13

    Any idea?

    use this UDF

    Code:
    Function RemoveApha(cellInput) As String           
        Set cellInput = Intersect(cellInput.Parent.UsedRange, cellInput)
        
        For i = 1 To Len(cellInput)
            Select Case Mid(cellInput, i, 1)
                Case 1, 2, 3, 4, 5, 6, 7, 8, 9, 0, " ", "-", "/"
                    Charval = Mid(cellInput, i, 1)
                Case Else
                    Charval = ""
            End Select
        RemoveApha = RemoveApha & Charval
        Next i
            
    End Function

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

    Default Re: Extract Only Numbers From Text String

    Quote Originally Posted by jamilm View Post
    use this UDF
    Try your UDF with this text "a1b2c3d(2/3)e4" and you will see why I asked the questions that I did in Message #44.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  7. #47
    Board Regular
    Join Date
    Jul 2011
    Location
    Manhattan
    Posts
    740
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract Only Numbers From Text String

    Quote Originally Posted by Rick Rothstein View Post
    Try your UDF with this text "a1b2c3d(2/3)e4" and you will see why I asked the questions that I did in Message #44.

    Hi Rick,

    i used the UDF and i got
    1232/34

    i thought the oddchecker is looking for to remove all normal alpha char and then retain numbers and that special chars.

    or perhaps i misunderstood.

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

    Default Re: Extract Only Numbers From Text String

    Quote Originally Posted by jamilm View Post
    Hi Rick,

    i used the UDF and i got
    1232/34

    i thought the oddchecker is looking for to remove all normal alpha char and then retain numbers and that special chars.

    or perhaps i misunderstood.
    I got the impression he was looking for a specific number. Because of our different interpretations is why I said "Unfortunately one example is usually not enough to decide on a course of action..." The question comes up, though... if the OP really wanted numbers only, then why keep the slash? What about decimal points, thousands separators, plus minus signs, currency symbols, etc. Hopefully the OP will be along shortly to clarify what his question actually is.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  9. #49
    New Member
    Join Date
    Nov 2014
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract Only Numbers From Text String

    Thanks Seniors for your great formulas.

    I am looking for if this brain storming can provide me an answer for this 'text string' as well.

    Fail David Simon; if achieved of marks 560: St. John College _______I want 560 in B1

    Pass Julie D'souza of high school; if got marks 75: Michigan College_______I want 75 in B2


    Regards
    Ravinder Ozha, PhD

  10. #50
    Board Regular
    Join Date
    Jul 2009
    Location
    Woking UK
    Posts
    2,756
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract Only Numbers From Text String

    @ravinderozha

    The formula in post #37 works fine.
    Mike

    -----------------------------------------------
    Some solutions don't require an IF!

    When posting code wrap your code between [CODE] tags, eg. [ code ] your code [ /code] - no spaces .

    Paste your Excel data...

    MrExcel HTML Maker or Excel Jeanie

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
  •