+ Reply to Thread
Results 1 to 34 of 34

How to extract special numbers from a column

  1. #1
    Registered User
    Join Date
    03-21-2012
    Location
    Karachi, Pakistan
    MS-Off Ver
    Excel 2010
    Posts
    81

    How to extract special numbers from a column

    Dear friends

    I am a beginner in excel. i have different mobile companies numbers
    which is starts from 4 digits as shown below:

    0300, 0301, 0302, 0303, 0304, 0305, 0306, 0307, 0308, 0309
    0341, 0342, 0343, 0344, 0345, 0346, 0347
    0331, 0332, 0333, 0334, 0335, 0336
    0321, 0322, 0323, 0324, 0325
    0311, 0312, 0313, 0314, 0315

    after the above 4 digit coding 7 digit number starts as below
    03001234567

    i have these mobile numbers data in million in one column
    so i want to extract those number those are repeating same
    numbers in 7 digit code which mobile companies use for
    postpaid clients like below sample:

    03008777119
    03001000110
    03001010101
    03008887756

    corporate numbers have some similar numbers in different
    scenarios how can i extract that type special numbers from
    that column.

    I hope that any expert person can make this formula or module.

    Attached another sample file to understand easily what i want.

    Regards
    Shoaib Ali
    Attached Files Attached Files
    Last edited by it_electronics2000; 04-26-2017 at 06:12 AM.

  2. #2
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

    Re: How to extract special numbers from a column

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    Thanks,
    Mike

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.

  3. #3
    Registered User
    Join Date
    03-21-2012
    Location
    Karachi, Pakistan
    MS-Off Ver
    Excel 2010
    Posts
    81

    Re: How to extract special numbers from a column

    I attached sample file because original file is too heavy

  4. #4
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,198

    Re: How to extract special numbers from a column

    It is not clear what you want. you have not shown what results you expect from your file. If you provide what you are after, someone will give you a quicker answer.

  5. #5
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Microsoft Office 2013
    Posts
    3,215

    Re: How to extract special numbers from a column

    What is criteria of special number. As per your data their is nothing to see special.


    atul


    If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.

    Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".

  6. #6
    Registered User
    Join Date
    03-21-2012
    Location
    Karachi, Pakistan
    MS-Off Ver
    Excel 2010
    Posts
    81

    Re: How to extract special numbers from a column

    Ok now i attached another sample file plz check.
    Attached Files Attached Files

  7. #7
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,198

    Re: How to extract special numbers from a column

    Still no clearer. Are the 2 files any different. You have not answered any of the questions for people to help you

  8. #8
    Registered User
    Join Date
    03-21-2012
    Location
    Karachi, Pakistan
    MS-Off Ver
    Excel 2010
    Posts
    81

    Re: How to extract special numbers from a column

    Special number means those have 3 or 4 digits same like below.

    03001000069

    this is a special number because after (0300) mobile company code we have 4 times 0 in 7 digits number.

    I want to extract these type of numbers those digits are repeating 3 to 4 times even if it is like that below:

    03004255655

    in this criteria we found 5 four times but it is separated by 6 but i want to extract these type of numbers also.
    Last edited by it_electronics2000; 04-27-2017 at 04:36 AM.

  9. #9
    Registered User
    Join Date
    03-21-2012
    Location
    Karachi, Pakistan
    MS-Off Ver
    Excel 2010
    Posts
    81

    Re: How to extract special numbers from a column

    first four digits of numbers are mobile company codes after that 7 digits are the numbers which i want to recognize by formula those are repeating same 3 to 4 digits in 7 digit numbers for extraction of whole mobile number with mobile company code.
    Last edited by it_electronics2000; 04-27-2017 at 04:36 AM.

  10. #10
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: How to extract special numbers from a column

    Hi,

    =IF(ROWS($1:1)>SUMPRODUCT(MMULT(N(7-LEN(SUBSTITUTE(RIGHT(A$3:A$54,7),{0,1,2,3,4,5,6,7,8,9},""))>2),{1;1;1;1;1;1;1;1;1;1})),"",TEXT(SMALL(IF(MMULT(N(7-LEN(SUBSTITUTE(RIGHT(A$3:A$54,7),{0,1,2,3,4,5,6,7,8,9},""))>2),{1;1;1;1;1;1;1;1;1;1}),0+A$3:A$54),ROWS($1:1)),REPT(0,11)))

    and copy down until you start to get blanks for the results.

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  11. #11
    Registered User
    Join Date
    03-21-2012
    Location
    Karachi, Pakistan
    MS-Off Ver
    Excel 2010
    Posts
    81

    Re: How to extract special numbers from a column

    When i applied this formula so i get this error and after that formula highlighted (RIGHT) ??
    Attached Images Attached Images

  12. #12
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: How to extract special numbers from a column

    Your profile says Excel 2010 - that error message is applicable to Excel 2003 and earlier, I believe.

    Or perhaps you are using Excel 2010 but mistakenly (or even intentionally) saved your current file as a .xls rather than .xlsx.

    Regards

  13. #13
    Registered User
    Join Date
    03-21-2012
    Location
    Karachi, Pakistan
    MS-Off Ver
    Excel 2010
    Posts
    81

    Re: How to extract special numbers from a column

    I have million numbers in each xlsx file and i have 340 files like that which i have to sort out these type of special numbers.
    In xls its not possible to do that with 65000 rows. I hope you understand ..

  14. #14
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: How to extract special numbers from a column

    Hi,

    Here is another attack on this problem. If we substitute blank with a letter (number in this case) in a string and compare the length of this with the original, we can tell how many letters were in the string. See the attached where I used this CSE formula to do this:

    =MAX(LEN($A3)-LEN(SUBSTITUTE($A3,{0,1,2,3,4,5,6,7,8,9},""))) -- This must be entered using a Control+Shift+Enter keystrokes.

    Then If you sort your numbers by this value the most repeated will come to the top. You could also do a Pivot, sorted by Max to get the same thing. See the attached.

    CSE Max Repeated numbers in a string .xlsx

    BTW - Change all the columns, except A, to General Format to have formulas calculate.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  15. #15
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: How to extract special numbers from a column

    Quote Originally Posted by it_electronics2000 View Post
    I have million numbers in each xlsx file and i have 340 files like that which i have to sort out these type of special numbers.
    In xls its not possible to do that with 65000 rows. I hope you understand ..
    You don't understand. The error message you get means that you must have saved that file as .xls, not .xlsx.

    Regards

  16. #16
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: How to extract special numbers from a column

    Quote Originally Posted by MarvinP View Post
    =MAX(LEN($A3)-LEN(SUBSTITUTE($A3,{0,1,2,3,4,5,6,7,8,9},""))) -- This must be entered using a Control+Shift+Enter keystrokes.
    Hi Marvin,

    FYI that doesn't require CSE.

    Regards

  17. #17
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: How to extract special numbers from a column

    All the better!!!

  18. #18
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: How to extract special numbers from a column

    Borrowing from MarvinP's approach this modification of the helper column formula ... non-array also.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Then this non-array formula to extract the target numbers.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Dave

  19. #19
    Registered User
    Join Date
    03-21-2012
    Location
    Karachi, Pakistan
    MS-Off Ver
    Excel 2010
    Posts
    81

    Re: How to extract special numbers from a column

    Yeah i understand. This is a sample file which is i uploaded i have all files in xlsx which contains 1 million numbers if i saved these files in xls so my 1 million rows shorten by xls into 65000 rows only because xls file extension have only 65000 rows so it is not possible for me to split these xlsx files in to 65000 rows xls files. this formula is not workable for me. hope you understand.

  20. #20
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: How to extract special numbers from a column

    I'm sorry, but I have no idea what you are saying.

    To clarify:

    The formula I gave you will not work if the file is saved as .xls
    The formula I gave you will work if the file is saved as .xlsx

    Regards

  21. #21
    Registered User
    Join Date
    03-21-2012
    Location
    Karachi, Pakistan
    MS-Off Ver
    Excel 2010
    Posts
    81

    Re: How to extract special numbers from a column

    Dear Flame

    Thanks for your working.

    It's mean i have to apply this formula in B Column
    =IF(MAX(LEN(RIGHT(A3,7))-LEN(SUBSTITUTE(RIGHT(A3,7),{0,1,2,3,4,5,6,7,8,9},"")))>2,ROW()-ROW($3:$3)+1,"")

    and after that i have to apply this formula in C Column
    =IFERROR(INDEX($A$3:$A$54,SMALL($B$3:$B$54,ROW()-ROW($3:$3)+1)),"")

    so i get all those numbers those have 3 or 4 digits same digits except first four mobile code company because mobile code is 0300 or 0333 so i want to collect these numbers without mobile company codes because ihave to sort out these numbers from after 7 digits numbers & maybe your formula works perfectly. as i attached sample file for you.
    Attached Files Attached Files

  22. #22
    Registered User
    Join Date
    03-21-2012
    Location
    Karachi, Pakistan
    MS-Off Ver
    Excel 2010
    Posts
    81

    Re: How to extract special numbers from a column

    Dear XOR LX

    Now i understand and apply your formula so i got many numbers which are not special
    like the list below which i extracted the bold numbers are not special numbers:

    03001000010
    03008666614
    03002106368
    03008461646
    03008355414
    03037351442

    03037351442
    03008635004
    03008231786
    03008550008
    03008451934
    03008550651
    03008233417
    03334328840
    03204274740
    03008634555
    03008486075
    03225111115
    03459170069
    03008247810

    Flame Formula Works better as below:
    03001000010
    03008666614
    03008461646
    03008550008
    03008550651
    03204274740
    03008634555
    03225111115
    03008733537
    03008487085
    03008446446
    03008631333
    03228444927
    03008277988
    03218442495
    03008440222
    03332101040
    03008214272
    03008225551
    03008467898
    03008441441
    03008223327
    03008222010
    03334338383
    03238666448
    03018666448
    03008400003

  23. #23
    Registered User
    Join Date
    03-21-2012
    Location
    Karachi, Pakistan
    MS-Off Ver
    Excel 2010
    Posts
    81

    Re: How to extract special numbers from a column

    Thanks Flame & all of you for your efforts !!!

  24. #24
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: How to extract special numbers from a column

    so i get all those numbers those have 3 or 4 digits same digits except first four mobile code company because mobile code is 0300 or 0333 so i want to collect these numbers without mobile company codes because ihave to sort out these numbers from after 7 digits numbers & maybe your formula works perfectly. as i attached sample file for you.
    I still not certain I understand, but reading the patterns in both uploads and your initial post leads me to believe that this needs to be in the helper column B. Again I am borrowing from MarvinP's solution.

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

  25. #25
    Registered User
    Join Date
    03-21-2012
    Location
    Karachi, Pakistan
    MS-Off Ver
    Excel 2010
    Posts
    81

    Re: How to extract special numbers from a column

    Dear Flame

    Please check the attached file the formula is not working on this type numbers.

    The all mobile numbers lists are generated by myself as under:


    Starts: 0300 (Mobile company code) 0000001 (7 digit Numbers)
    End: 0300 (Mobile company code) 9999999 (7 digit Numbers)

    Starts: 0301 (Mobile company code) 0000001 (7 digit Numbers)
    End: 0301 (Mobile company code) 9999999 (7 digit Numbers)

    I have 33 Mobile company codes and each code have 10 million numbers as i show on above table.

    When i applied this formula on first million list which is starts from 03000000001 so i get nothing. Maybe formula not counting 0 figures in numbers.
    Attached Files Attached Files
    Last edited by it_electronics2000; 05-05-2017 at 12:30 PM.

  26. #26
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: How to extract special numbers from a column

    it_electronics2000 the latest upload contains no data. It is a blank workbook.

    I tried the number 03000000001 pasted into the original workbook and the formula works fine.

    Somethings of note:

    The original file has text "numbers" for the phone numbers. This works fine as long as you maintain consistency.

    If you have entered numbers like 03000000001 as numbers Excel will drop the leading 0s. This can defeat the helper formula that evaluates the first 4 digits of a text number.

    If that does not help try uploading again with samples of the trouble data.
    Last edited by FlameRetired; 05-06-2017 at 10:39 PM.

  27. #27
    Registered User
    Join Date
    03-21-2012
    Location
    Karachi, Pakistan
    MS-Off Ver
    Excel 2010
    Posts
    81

    Re: How to extract special numbers from a column

    Dear Flame

    Thanks for your kind reply. Attached please find the
    fie which shows some of blank lines which have 0 figures
    but formula not counting it.
    Attached Files Attached Files

  28. #28
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Microsoft Office 2013
    Posts
    3,215

    Re: How to extract special numbers from a column

    Look attach file.
    I have tried on some different way. But i also not satisfied on this file. If anyone can modified / adding in this welcome.
    i extract number based on criteria of special number (continuously digit)
    Attached Files Attached Files

  29. #29
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: How to extract special numbers from a column

    it_electronics2000 in the latest upload the formulas need to be edited to reference the correct cells and ranges. Also of note I used a method

    ROW()-ROW($1:$1)+1 to index the qualifying numbers in column A and for the k argument of SMALL. Normally I use the simpler ROWS($1:1), but you mentioned there are
    i have these mobile numbers data in million in one column
    I find that ROW()-ROW($1:$1)+1 works noticeably faster with large amounts of output. There is a down side to this practice in that the -ROW($1:$1) must be the row number of the first row the formula is entered into.

    I tried these edited formulas and they work fine.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  30. #30
    Registered User
    Join Date
    03-21-2012
    Location
    Karachi, Pakistan
    MS-Off Ver
    Excel 2010
    Posts
    81

    Re: How to extract special numbers from a column

    Dear Flame

    Thanks for your kind reply. I applied your formula yesterday
    but it takes time and still on 59% process you can see in
    attached picture.

    I changed the second formula as under:

    =IFERROR(INDEX($A$1:$A$1000000,SMALL($B$1:$B$1000000,ROW()-ROW($1:$1)+1)),"")

    i applied both formulas at a time on 1 million rows maybe that's why it takes time.
    Attached Images Attached Images

  31. #31
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: How to extract special numbers from a column

    i applied both formulas at a time on 1 million rows maybe that's why it takes time.
    It wouldn't help. I would complete the helper column calculations before endeavoring to apply the final formula (column C). Even then with over one million rows to process (just for the helper column B) it is going to take time.

    I know of no other approaches nor can I identify further bottlenecks in the calculations. I wish I could.

    Is it taking less time than previous approaches?

  32. #32
    Registered User
    Join Date
    03-21-2012
    Location
    Karachi, Pakistan
    MS-Off Ver
    Excel 2010
    Posts
    81

    Re: How to extract special numbers from a column

    Dear Dave

    I figure out some thing in C Column Formula. Can we make c formula which is only work on
    1 cell because c column formula covers whole 1 million cells as under:

    =IFERROR(INDEX($A$1:$A$1000000,SMALL($B$1:$B$1000000,ROW()-ROW($1:$1)+1)),"")

  33. #33
    Registered User
    Join Date
    03-21-2012
    Location
    Karachi, Pakistan
    MS-Off Ver
    Excel 2010
    Posts
    81

    Re: How to extract special numbers from a column

    No problem if blank cells comes in result C Column

  34. #34
    Registered User
    Join Date
    03-21-2012
    Location
    Karachi, Pakistan
    MS-Off Ver
    Excel 2010
    Posts
    81

    Re: How to extract special numbers from a column

    Dear Flame

    Can you please edit these below formulas from MS OFFICE 2010 to MS Office 2013 because 2013 version is faster than 2010 but these formulas are not working in MS Office 2013:

    apply formula on b Column
    =IF(MAX(LEN(RIGHT(A1,7))-LEN(SUBSTITUTE(RIGHT(A1,7),{0,1,2,3,4,5,6,7,8,9},"")))>2,ROW()-ROW($1:$1)+1,"")

    apply formula on C Column
    =IFERROR(INDEX($A$1:$A$1000000,SMALL($B$1:$B$1000000,ROW()-ROW($1:$1)+1)),"")

+ 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. extract dates from a column of numbers
    By freak11 in forum Excel General
    Replies: 10
    Last Post: 09-14-2013, 10:32 AM
  2. [SOLVED] Extract Numbers to new column
    By SamCrome in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-04-2013, 05:24 AM
  3. [SOLVED] Macro to extract numbers over 6 digets in column
    By abjac in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 01-25-2013, 05:46 AM
  4. Extract few numbers from columns and paste them in different column
    By trailblazers100 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-24-2013, 06:46 PM
  5. [SOLVED] Extract 1st and 2nd minimum numbers from a column
    By bobboy in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-16-2013, 05:25 PM
  6. Extract certain numbers from a column of text cells
    By yangliu2 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-05-2012, 10:19 PM
  7. programming a special column of numbers
    By bear23 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-15-2009, 10:52 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