+ Reply to Thread
Results 1 to 12 of 12

Count same digit numbers from cell

  1. #1
    Forum Contributor
    Join Date
    07-17-2020
    Location
    Uzbek
    MS-Off Ver
    Office 365, V 2212
    Posts
    196

    Count same digit numbers from cell

    Dear Excel Forum Members,

    i would like to count the same digit numbers from the cell so could you please provide formula for the same.
    For instance, there is a number in A1=714144 and the result after counting should be 23 because there are 2 same #1 and 3 same number 4

    thanks in advance for help

  2. #2
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Count same digit numbers from cell

    without more examples it's hard to be sure but, one (O365) option might be:

    A2: =TEXTJOIN("",TRUE,TEXT(FREQUENCY(SEARCH(MID($A1,ROW(A$1:INDEX(A:A,LEN($A1))),1),$A1),ROW(A$1:INDEX(A:A,LEN($A1)))),"[<2]"""";#"))
    confirmed with CTRL + SHIFT + ENTER
    change delimiter to ; if required

    edit -- another similar variant but which would return the frequency relative to numbers 0 through 9 (in that order)

    =TEXTJOIN("",TRUE,INDEX(TEXT(LEN(A1)-LEN(SUBSTITUTE(A1,ROW($1:$10)-1,"")),"[<2]"""";#"),0))
    change delimiter to ; if required

    to elaborate on the difference -- were A1 = 34599888 the first would return 23, and the second would return 32 -- the 2nd is different as it looks for each digit 0-9 in sequence, so 8 (3) is found before 9 (2)
    Last edited by XLent; 08-11-2020 at 11:34 AM.

  3. #3
    Forum Contributor
    Join Date
    07-17-2020
    Location
    Uzbek
    MS-Off Ver
    Office 365, V 2212
    Posts
    196

    Re: Count same digit numbers from cell

    Quote Originally Posted by XLent View Post
    without more examples it's hard to be sure but, one (O365) option might be:

    A2: =TEXTJOIN("",TRUE,TEXT(FREQUENCY(SEARCH(MID($A1,ROW(A$1:INDEX(A:A,LEN($A1))),1),$A1),ROW(A$1:INDEX(A:A,LEN($A1)))),"[<2]"""";#"))
    confirmed with CTRL + SHIFT + ENTER
    change delimiter to ; if required

    edit -- another similar variant but which would return the frequency relative to numbers 0 through 9 (in that order)

    =TEXTJOIN("",TRUE,INDEX(TEXT(LEN(A1)-LEN(SUBSTITUTE(A1,ROW($1:$10)-1,"")),"[<2]"""";#"),0))
    change delimiter to ; if required

    to elaborate on the difference -- were A1 = 34599888 the first would return 23, and the second would return 32 -- the 2nd is different as it looks for each digit 0-9 in sequence, so 8 (3) is found before 9 (2)
    Dear XLent,

    thanks for your feedback.
    can you please check the attached file, the provided formula is giving error

    thanks
    Attached Files Attached Files

  4. #4
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Count same digit numbers from cell

    Hi, the formula works as expected but you need to use as provided - it seems you modified the row anchor in the row to $2 rather than $1 -- this is unrelated to your dataset - and is being used to create an array from 1 to n (relative to length of text)
    {some people use ROW w/INDIRECT but that will introduce volatility)

    As you want numbers (and 0) I've tweaked slightly to:

    =(0&TEXTJOIN("",TRUE,TEXT(FREQUENCY(SEARCH(MID($A2,ROW(A$1:INDEX(A:A,LEN($A2))),1),$A2),ROW(A$1:INDEX(A:A,LEN($A2)))),"[<2]"""";#")))+0
    CSE

    I've corrected all of the above in your file and reloaded, this should auto-translate when you open

    note: per your profile I've assumed you're using Office 365 and thus have access to TEXTJOIN.

    on a final aside, please don't reply by quote as this clutters the board -- if you need to quote a specific piece do so, else just use Quick Reply etc.
    Attached Files Attached Files

  5. #5
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Count same digit numbers from cell

    Another one

    =--CONCAT(0,TEXT(FREQUENCY(--MID(A2,SEQUENCE(LEN(A2)),1),SEQUENCE(9)-1),"[>1]0;"))

  6. #6
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Count same digit numbers from cell

    Nice Bo_Ry - I don't have SEQUENCE available as yet (on my client), awaiting the semi-annual update to be rolled out at work....

  7. #7
    Forum Contributor
    Join Date
    07-17-2020
    Location
    Uzbek
    MS-Off Ver
    Office 365, V 2212
    Posts
    196

    Re: Count same digit numbers from cell

    Hi XLent,

    I'm using Excel 2013 and TEXTJOIN function is not available in that version so is there any other solution plz

    thanks

  8. #8
    Forum Contributor
    Join Date
    07-17-2020
    Location
    Uzbek
    MS-Off Ver
    Office 365, V 2212
    Posts
    196

    Re: Count same digit numbers from cell

    Quote Originally Posted by Bo_Ry View Post
    Another one

    =--CONCAT(0,TEXT(FREQUENCY(--MID(A2,SEQUENCE(LEN(A2)),1),SEQUENCE(9)-1),"[>1]0;"))
    Hi Bo_Ry

    thanks for the feedback.

    Am using office 2013 and the provided formula is not working so plz provide any alternative option if possible

    thanks

  9. #9
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Count same digit numbers from cell

    Thanks XLent

    @Gulya
    We make formula base on your profile MS version, Please update your profile.
    try this at B2

    =--(0&SUBSTITUTE(SUBSTITUTE(SUMPRODUCT(FREQUENCY(--MID(A2,ROW(A$1:INDEX(A:A,LEN(A2))),1),ROW(A$1:A$9)-1)*10^(10-ROW(A$1:A$10))),0,),1,))
    Attached Files Attached Files

  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: Count same digit numbers from cell

    Another option, array formula**:

    =NPV(-0.9,IFERROR(TEXT(LEN(A2)-LEN(SUBSTITUTE(A2,{9,8,7,6,5,4,3,2,1},"")),"[<2]ζ")/10,""))

    Regards


    **Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
    Click * below if this answer helped

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

  11. #11
    Forum Contributor
    Join Date
    07-17-2020
    Location
    Uzbek
    MS-Off Ver
    Office 365, V 2212
    Posts
    196

    Re: Count same digit numbers from cell

    Quote Originally Posted by Bo_Ry View Post
    Thanks XLent

    @Gulya
    We make formula base on your profile MS version, Please update your profile.
    try this at B2

    =--(0&SUBSTITUTE(SUBSTITUTE(SUMPRODUCT(FREQUENCY(--MID(A2,ROW(A$1:INDEX(A:A,LEN(A2))),1),ROW(A$1:A$9)-1)*10^(10-ROW(A$1:A$10))),0,),1,))
    thanks a lot Bo_Ry, working perfectly

  12. #12
    Forum Contributor
    Join Date
    07-17-2020
    Location
    Uzbek
    MS-Off Ver
    Office 365, V 2212
    Posts
    196

    Re: Count same digit numbers from cell

    Quote Originally Posted by XOR LX View Post
    Another option, array formula**:

    =NPV(-0.9,IFERROR(TEXT(LEN(A2)-LEN(SUBSTITUTE(A2,{9,8,7,6,5,4,3,2,1},"")),"[<2]ζ")/10,""))

    Regards


    **Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
    thanks a lot XOR LX, working perfectly as expected

+ 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] Count same last digit numbers and neighbouring numbers
    By Gulya in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 07-20-2020, 05:47 PM
  2. Replies: 1
    Last Post: 04-18-2019, 01:35 AM
  3. [SOLVED] Macro to convert WBS single digit numbers to double digit numbers
    By LeanAccountant in forum Excel Programming / VBA / Macros
    Replies: 20
    Last Post: 06-09-2018, 05:25 AM
  4. [SOLVED] Formula for getting the digit count between two numbers?
    By mix_mash in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-01-2017, 06:11 PM
  5. [SOLVED] Need to count only 6 digit numbers
    By Sekars in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 06-02-2017, 10:47 AM
  6. [SOLVED] macro to extract 9 digit numbers as well as alpha numberic 9 digit numbers from txt file
    By Raju Radhakrishnan in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-13-2012, 10:15 AM
  7. To count numbers with 4 digit/5 digit with given Range.
    By ebin charles in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-15-2012, 08:49 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