+ Reply to Thread
Results 1 to 15 of 15

Need to extract the 10 digit part number

  1. #1
    Forum Contributor
    Join Date
    02-15-2016
    Location
    India
    MS-Off Ver
    2016
    Posts
    657

    Need to extract the 10 digit part number

    Hello Friends

    In the cell A1 text input is given, in light yellow cells D2:D12 the 9 digit part number in the format of XXXX-XXXXX need to be extracted.

    thanks in advance.
    Sekar

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Need to extract the 10 digit part number

    Is this data representative: are there always 4 numbers?

  3. #3
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: Need to extract the 10 digit part number

    1 is always the first in a group?

  4. #4
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Need to extract the 10 digit part number

    hi Sekars. try:
    =IFERROR(MID($A$1,SEARCH("????-?????",$A$1,IFERROR(SEARCH(D1,$A$1),1)+10),10),"")

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  5. #5
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Need to extract the 10 digit part number

    You can do that with PowerQuery add-in for Excel 2010
    Last edited by sandy666; 09-28-2017 at 07:01 AM. Reason: file updated for more flexibility

  6. #6
    Forum Contributor
    Join Date
    02-15-2016
    Location
    India
    MS-Off Ver
    2016
    Posts
    657

    Re: Need to extract the 10 digit part number

    Hello all

    here the conditions is first 4 numbers then `-' hyphen the next 5 numbers will come. First number not always 1, it will be vary form 0 to 9, again the syntax is XXXX-XXXXX where X is 0 to 9.

  7. #7
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Need to extract the 10 digit part number

    but "-" always exist? if yes see post #5

  8. #8
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Need to extract the 10 digit part number

    D2=MID($A$1,SMALL(INDEX((ISNUMBER(MID($A$1,ROW(INDIRECT("1:"&LEN($A$1)-9)),4)+0)+(MID($A$1,ROW(INDIRECT("5:"&LEN($A$1)-5)),1)="-")+ISNUMBER(MID($A$1,ROW(INDIRECT("6:"&LEN($A$1)-4)),5)+0)<>3)*10^3+ROW(INDIRECT("1:"&LEN($A$1)-9)),0),ROWS(D$2:D2)),10)
    Try this and copy towards down
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  9. #9
    Forum Contributor
    Join Date
    02-15-2016
    Location
    India
    MS-Off Ver
    2016
    Posts
    657

    Re: Need to extract the 10 digit part number

    Thank to all who have replied for this post.

    Benishiryo – It looks like circular looped, Numbers are endlessly going,
    Sandy666 - Ito be frank I do know much about Power Query and need to google it and need to be used. Will try it.
    Siva – It pulls texts also and again endless

    Thanks again

  10. #10
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Need to extract the 10 digit part number

    it works for me see the attached file

  11. #11
    Forum Contributor
    Join Date
    02-15-2016
    Location
    India
    MS-Off Ver
    2016
    Posts
    657

    Re: Need to extract the 10 digit part number

    yes, in your file its fine, but in real application it pulls the text also instead of 10 digit part numbers.

    Please refer the attached file.

    thanks again for your formula.

  12. #12
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Need to extract the 10 digit part number

    I copied only value from your last file and pasted into my file then refresh QueryTable

  13. #13
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Need to extract the 10 digit part number

    d2=MID($A$1,SMALL(INDEX((ISNUMBER(MID($A$1,ROW(INDIRECT("1:"&LEN($A$1)-9)),4)+0)+(MID($A$1,ROW(INDIRECT("5:"&LEN($A$1)-5)),1)="-")+ISNUMBER(MID($A$1,ROW(INDIRECT("6:"&LEN($A$1)-4)),5)+0)<>3)*LEN($A$1)+ROW(INDIRECT("1:"&LEN($A$1)-9)),0),ROWS(D$2:D2)),10)

    earlier Considered maximum text length as 10*3 = 1000 in your case it is around 7200 characters that's why problem occor
    try above formula and copy towards down

  14. #14
    Forum Contributor
    Join Date
    02-15-2016
    Location
    India
    MS-Off Ver
    2016
    Posts
    657

    Re: Need to extract the 10 digit part number

    Siva - Thanks, Now it works fine, but need to test in real application it will take more time. Hope no issues.

    Thanks to all who replied / posted for my post.

    Thanks again.

  15. #15
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Need to extract the 10 digit part number

    You are welcome

+ 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] Keep or extract only 6 digit number
    By Sekars in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-07-2017, 03:48 AM
  2. Extract first 5 digit number starting from the right
    By Cboggie in forum Excel General
    Replies: 5
    Last Post: 05-09-2014, 01:17 PM
  3. How to extract a 15 or 16 digit number from an email.
    By kaushalnaman in forum Excel General
    Replies: 4
    Last Post: 02-22-2014, 05:25 PM
  4. Delete the entire column that has single digit number - Part-2
    By plasma33 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-18-2012, 01:44 AM
  5. Extract 9 digit number from string
    By Jbentley in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 11-16-2011, 07:02 AM
  6. Extract 10 digit number from string
    By R. Choate in forum Excel Programming / VBA / Macros
    Replies: 20
    Last Post: 10-27-2005, 06:05 PM
  7. [SOLVED] Extract 2, 3, 4 or 5-digit number from string
    By Jim in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-07-2005, 10:22 AM

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