+ Reply to Thread
Results 1 to 9 of 9

Extract unique value from number

  1. 12-03-2014, 01:36 PM #1
    sumansau007
    sumansau007 is offline
    Registered User
    Join Date
    06-25-2014
    Posts
    4

    Extract unique value from number

    I have a different type of data in a particular cell..
    Ex:
    Good books ltd 004bm14356890001 Mauritius.

    Now i have to extract the unique no of 16 digits.

    Now few things...
    I have such data approx 50000 rows..
    .unique number can appear anywhere in the data, either in beginning or middle or at the end.....
    The unique number can begin with any number or text....

    Common thing is 16 digit length....


    Please provide me a formula to get the result.

    I have tried
    =if(A2,Find("00", A2),16)

    This formula is limited to beginwith condition. Please advise if this possible to avoid this.


    Sent from my GT-I9082 using Tapatalk
    Last edited by FDibbins; 12-04-2014 at 12:47 PM.
    Reply With QuoteRegister To Reply
  2. 12-03-2014, 02:12 PM #2
    Glenn Kennedy
    Glenn Kennedy is online now
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,117

    Re: Please give me a way

    This is horrible, but it works...

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



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
    Reply With QuoteRegister To Reply
  3. 12-03-2014, 02:13 PM #3
    Jonmo1
    Jonmo1 is offline
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Please give me a way

    Here's another hideous but functional formula

    =MID(A1,LOOKUP(2,1/(MID(A1,ROW(A$1:INDEX(A:A,LEN(A1)-15)),16)=SUBSTITUTE(MID(A1,ROW(A$1:INDEX(A:A,LEN(A1)-15)),16)," ","")),ROW(A$1:INDEX(A:A,LEN(A1)-15))),16)
    Reply With QuoteRegister To Reply
  4. 12-03-2014, 02:13 PM #4
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,945

    Re: Please give me a way

    Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem (think google search terms?). Once you have done this please send me a PM and I will remove this request. (Also, include a link to your thread - copy from the address bar)

    Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?

    To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  5. 12-03-2014, 02:22 PM #5
    Glenn Kennedy
    Glenn Kennedy is online now
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,117

    Re: Please give me a way

    Apologies - I shouldn't have posted a response on a thread with such a poor title. Please take the time to change it to something meaningful, especially if Jonmo1's reply, or mine, helped solve your problem.
    Reply With QuoteRegister To Reply
  6. 12-04-2014, 12:30 PM #6
    sumansau007
    sumansau007 is offline
    Registered User
    Join Date
    06-25-2014
    Posts
    4

    Re: Please give me a way

    Quote Originally Posted by FDibbins View Post
    Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem (think google search terms?). Once you have done this please send me a PM and I will remove this request. (Also, include a link to your thread - copy from the address bar)

    Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?

    To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.
    Sorry for the inconvenience caused.. I am too newbie to this forum. I am unable to edit the title. Moderators can anyone help me to change the title.

    Friends please help me to resolve the issue. Thanks in advance.

    Sent from my GT-I9082 using Tapatalk
    Reply With QuoteRegister To Reply
  7. 12-04-2014, 12:48 PM #7
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,945

    Re: Extract unique value from number

    I have changed your title for you - this time. My post explained quite clearly how to do that. For future reference, scroll up to your 1st post, click EDIT POST then Go Advanced and change your title there
    Reply With QuoteRegister To Reply
  8. 12-06-2014, 01:00 PM #8
    sumansau007
    sumansau007 is offline
    Registered User
    Join Date
    06-25-2014
    Posts
    4

    Re: Please give me a way

    Thanks bro, this formula is working almost for 80%... anyway thanks for help.. it saves a lot of time for me..

    for yor info, i got this result for below data as shown below

    Good books ltd 004bm14356890001Mauritius. = 890001Mauritius.

    can you please check and confirm any way-out for this.

    @Also thanks to Glenn Kennedy bro, tried your formula also and got correct result in few cases.
    Reply With QuoteRegister To Reply
  9. 12-08-2014, 06:42 AM #9
    István Hirsch
    István Hirsch is offline
    Registered User
    Join Date
    05-10-2013
    Location
    Pomáz, Hungary
    MS-Off Ver
    Excel 2007
    Posts
    78

    Re: Extract unique value from number

    Try this UDF and let me know if it does not work as you need and why:

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] If Statements (?) - If Cell1 =yes then give -Cell2, If Cell1=no then give +Cell2
    By KTXD in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-02-2012, 12:24 AM
  2. IF give or take
    By lky2k23 in forum Excel General
    Replies: 7
    Last Post: 10-21-2011, 09:25 AM
  3. Give RELEVANT responses to questions. DO NOT give usless list
    By pmartin in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-06-2006, 02:00 PM
  4. [SOLVED] Give help
    By heinze22 in forum Excel General
    Replies: 2
    Last Post: 04-26-2006, 09:55 AM
  5. I give up! Help Please...
    By Dino in forum Excel General
    Replies: 11
    Last Post: 06-01-2005, 11:29 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1