+ Reply to Thread
Results 1 to 11 of 11

Extracting number from a text string

  1. #1
    Registered User
    Join Date
    10-02-2013
    Location
    Kildar
    MS-Off Ver
    Office365 ProPlus
    Posts
    13

    Extracting number from a text string

    Hi! Already found a partial solution to my problem from this board - I need to extract 8-digit number from a text string. Quite like discussed in another thread (unfortunately can't post a link "Finding numbers in text string").
    Option provided there by daddylonglegs partially matches my needs:

    Please Login or Register  to view this content.
    However this formula processes the string starting from the back (from right to left), but I'd need it to work left-to-right. So from a string of "Data 10569228 AND 11112365 00671990000105729657 String continuing"
    I'd need to grab "10569228", however this formula currently grabs "05729657"

    Could you please help me out? I'm having a hard time understanding this one. If it was able to ignore non-8 digit numbers, even better, but that's less important. Thanks a lot!

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,937

    Re: Extracting number from a text string

    Does this help?

    In B1
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    A
    B
    1
    "Data 10569228 AND 11112365 00671990000105729657 String continuing"
    10569228
    Dave

  3. #3
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,937

    Re: Extracting number from a text string

    Or with string in A2
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,489

    Re: Extracting number from a text string

    What does this mean?

    If it was able to ignore non-8 digit numbers, even better, ...
    The likelihood of us being able to crack this for you based on just one example of a text string you need to process is low. If neither of the solutions offered work for you, please provide a sample workbook with at least 10-15 rows of data for us to work with, showing the variations we might see across your entire dataset.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  5. #5
    Registered User
    Join Date
    10-02-2013
    Location
    Kildar
    MS-Off Ver
    Office365 ProPlus
    Posts
    13

    Re: Extracting number from a text string

    deleted message

  6. #6
    Registered User
    Join Date
    10-02-2013
    Location
    Kildar
    MS-Off Ver
    Office365 ProPlus
    Posts
    13

    Re: Extracting number from a text string

    Quote Originally Posted by FlameRetired View Post
    Does this help?

    In B1
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    A
    B
    1
    "Data 10569228 AND 11112365 00671990000105729657 String continuing"
    10569228
    Thanks but unfortunately not. It does work with the example I sent; however this 8-digit number can be located in any position within the string. For an example this code produces result NO from string INVOICE NO 10572296 ThisCompany LTD. Meanwhile I'm looking for 10572296

    The code in my OP would work fine with this string; however if the string was INVOICE NO 10572296 AND 10572297 ThisCompany LTD; it would grab 10572297 instead of 10572296

  7. #7
    Registered User
    Join Date
    10-02-2013
    Location
    Kildar
    MS-Off Ver
    Office365 ProPlus
    Posts
    13

    Re: Extracting number from a text string

    Quote Originally Posted by FlameRetired View Post
    Or with string in A2
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Similar issues with this one. VOICE NO is grabbed from INVOICE NO 10572296 ThisCompany LTD

  8. #8
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2010
    Posts
    4,793

    Re: Extracting number from a text string

    Try this:

    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    10-02-2013
    Location
    Kildar
    MS-Off Ver
    Office365 ProPlus
    Posts
    13

    Re: Extracting number from a text string

    Thanks a lot. Looks to be working just as I needed!

    A funny thing - I mistakenly copied just a part of the formula at first to try it out with my data and looks to be working just as well:
    Please Login or Register  to view this content.

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,489

    Re: Extracting number from a text string

    I think that must be a fluke - are you SURE that thiis all there is in the cell? Expand the formula bar to check - maybe the rest of the formula is split over several lines. It really should not work as you have shown it - it should throw a syntax error.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  11. #11
    Registered User
    Join Date
    10-02-2013
    Location
    Kildar
    MS-Off Ver
    Office365 ProPlus
    Posts
    13

    Re: Extracting number from a text string

    AliGW - you are right, it doesn't work like that. I must have seen an awkwardly cut snippet of formula on my formula bar - should have dragged the formula box bigger to be sure. Sorry, will mark the thread as solved - I'm new to the board, didn't notice that.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Extracting A Number From A String Of Text
    By @MeDaveT in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-15-2013, 02:57 PM
  2. Replies: 12
    Last Post: 03-20-2013, 05:46 PM
  3. Extracting numbers and text from inconsistent text/number formatted string
    By Brandivil in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-25-2013, 01:46 PM
  4. Extracting a number (part of a series of numbers) from a text string
    By dannyjoer in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-09-2012, 10:17 AM
  5. Replies: 6
    Last Post: 07-29-2008, 03:23 PM
  6. Extracting a number from a string
    By dsexpress in forum Excel General
    Replies: 3
    Last Post: 04-26-2008, 08:41 AM
  7. Extracting numbers from a combined text & number string
    By astrikor in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-25-2007, 08:45 AM
  8. [SOLVED] Extracting a number in a text string
    By Pogo in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-17-2006, 01:45 PM

Tags for this Thread

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