Page 1 of 3 123 LastLast
Results 1 to 10 of 27

Thread: Extract whole number from string

  1. #1
    Board Regular sharky12345's Avatar
    Join Date
    Aug 2010
    Posts
    3,067
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Extract whole number from string

    I'm having issues trying to extract a number from a string.

    I've currently got this array formula;

    =IF(A11="","",(SUMPRODUCT(MID(0&A11,LARGE(INDEX(ISNUMBER(--MID(A11,ROW($1:$44),1))*ROW($1:$44),0),ROW($1:$44))+1,1)*10^ROW($1:$44)/10)))
    The issue is that there are various numbers at various points within the string and what I need to do is extract only the number which will always be on it's own, like this;

    GD 12345 GTH1
    In this case I'd want to extract 12345 and nothing else, so ignoring any number which is attached to a letter. There will never be more than 1 'string of numbers' with nothing else, but the number of characters either before or after the number I want will vary, so I need to take that into account please.
    There isn't a day go by without me learning something new about VBA!

  2. #2
    Board Regular
    Join Date
    Mar 2010
    Location
    Bradford, West Yorkshire, UK
    Posts
    2,391
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Extract whole number from string

    sharky, not to detract from formulas but have you tried a separate column to the information with flashfill on the data tab? Excel 2010> I think

    when it works it really is very good for things like this
    I visit this site mainly to remember how little I know

  3. #3
    Rules Violation
    Join Date
    Oct 2002
    Location
    Turkey
    Posts
    1,075
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Extract whole number from string

    @sharky12345;

    If the data is in cell A11, you can try this;

    Code:
    =TRIM(MID(SUBSTITUTE(A11," ",REPT(" ",100)),100,100))
    Last edited by Haluk; Feb 17th, 2018 at 11:37 AM.

  4. #4
    MrExcel MVP
    Join Date
    May 2011
    Posts
    3,699
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Extract whole number from string

    Quote Originally Posted by sharky12345 View Post
    I'm having issues trying to extract a number from a string.

    I've currently got this array formula;

    The issue is that there are various numbers at various points within the string and what I need to do is extract only the number which will always be on it's own, like this;

    In this case I'd want to extract 12345 and nothing else, so ignoring any number which is attached to a letter. There will never be more than 1 'string of numbers' with nothing else, but the number of characters either before or after the number I want will vary, so I need to take that into account please.
    Maybe the Array Formula (use Ctrl+Shift+Enter to enter the formula) below can help you:

    =IF(A1="","",SUM(IFERROR(--MID(SUBSTITUTE(A1," ",REPT(" ",99)),1+99*(ROW(INDIRECT("1:50"))-1),99),0)))

    Markmzz

  5. #5
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    32,516
    Post Thanks / Like
    Mentioned
    52 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Extract whole number from string

    [QUOTE=sharky12345;5011114]I'm having issues trying to extract a number from a string.

    The issue is that there are various numbers at various points within the string and what I need to do is extract only the number which will always be on it's own, like this;

    GD 12345 GTH1
    Is your number always located between the second and third blank space?

    If not, can the number ever be located as the beginning or end of the text?
    Last edited by Rick Rothstein; Feb 17th, 2018 at 01:28 PM.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  6. #6
    Board Regular sharky12345's Avatar
    Join Date
    Aug 2010
    Posts
    3,067
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract whole number from string

    Thanks for the replies.....

    Rick - it can be anywhere I'm afraid....
    There isn't a day go by without me learning something new about VBA!

  7. #7
    Board Regular sharky12345's Avatar
    Join Date
    Aug 2010
    Posts
    3,067
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract whole number from string

    Haluk - your suggestion results in text from the cell, so not what I was expecting.

    Markmzz - your suggestion results in a zero, so not quite sure what's happened there.

    For info this is the value in the cell I've tried these suggestions in;

    G.T. 27415 FGTYITFSDDI (Z2)(UHSG)
    There isn't a day go by without me learning something new about VBA!

  8. #8
    MrExcel MVP
    Join Date
    May 2011
    Posts
    3,699
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Extract whole number from string

    Quote Originally Posted by sharky12345 View Post

    Markmzz - your suggestion results in a zero, so not quite sure what's happened there.

    For info this is the value in the cell I've tried these suggestions in;
    Here all is Ok. Did you use Ctrl+Shift+Enter to enter the formula?

    Markmzz

  9. #9
    Rules Violation
    Join Date
    Oct 2002
    Location
    Turkey
    Posts
    1,075
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Extract whole number from string

    Quote Originally Posted by sharky12345 View Post

    For info this is the value in the cell I've tried these suggestions in;

    G.T. 27415 FGTYITFSDDI (Z2)(UHSG)

    The formula i suggested extracts 27415 from the string. Isn't it the value you are looking for?


    Last edited by Haluk; Feb 17th, 2018 at 02:59 PM.

  10. #10
    Rules Violation
    Join Date
    Oct 2002
    Location
    Turkey
    Posts
    1,075
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Extract whole number from string

    You can add + 0 to the end of the formula, if you want to get the result as a number.

    Code:
    =TRIM(MID(SUBSTITUTE(A11," ",REPT(" ",100)),100,100)) +0
    Last edited by Haluk; Feb 17th, 2018 at 03:16 PM.

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
  •