+ Reply to Thread
Results 1 to 5 of 5

Extract number out of a string

  1. #1
    Forum Contributor
    Join Date
    11-02-2007
    Location
    Bryne, Norway
    MS-Off Ver
    Microsoft Office Excel 2010
    Posts
    230

    Extract number out of a string

    I need a function who extract the number out of the string below. The amount can be different from the example. There is always a thousand seperator and the text can be other single words.

    5 000 Gross
    Last edited by mkvassh; 04-07-2010 at 05:25 PM.

  2. #2
    Forum Expert contaminated's Avatar
    Join Date
    05-07-2009
    Location
    Baku, Azerbaijan
    MS-Off Ver
    Excel 2013
    Posts
    1,430

    Re: Extract number out of a string

    Try
    =1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$9),1)),0),COUNT(1*MID(A1,ROW($1:$9),1)))

    Where A1 is the cell with text
    PS Confirm w/ Ctrl+Shift+Enter
    Last edited by contaminated; 04-07-2010 at 05:14 PM.
    Люди, питающие благие намерения, как раз и становятся чудовищами.

    Regards, ?Born in USSR?
    Vusal M Dadashev

    Baku, Azerbaijan

  3. #3
    Forum Contributor
    Join Date
    11-02-2007
    Location
    Bryne, Norway
    MS-Off Ver
    Microsoft Office Excel 2010
    Posts
    230

    Re: Extract number out of a string

    It will not work. Can you attach a spreadsheet with your example?

  4. #4
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: Extract number out of a string

    mkvassh,

    See the attached workbook "Extract number or text out of a string - mkvassh - SDG10.xls" with Function "TextNum".
    Have a great day,
    Stan

    Windows 10, Excel 2007, on a PC.

    If you are satisfied with the solution(s) provided, please mark your thread as Solved by clicking EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

  5. #5
    Forum Contributor
    Join Date
    11-02-2007
    Location
    Bryne, Norway
    MS-Off Ver
    Microsoft Office Excel 2010
    Posts
    230

    Re: Extract number out of a string

    Thanks a lot :-)

    Regards,
    Magne

  6. #6
    Registered User
    Join Date
    03-11-2014
    Location
    Chennai
    MS-Off Ver
    Excel 2010
    Posts
    0

    Re: Extract number out of a string

    Thanks for the formula. . But me having numbers in fractions. . such as 5.5,65.5 etc. . could you plz help on this issue. . ??

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1