Find out if a number has repetitive digits [formula homework]

Posted on October 3rd, 2017 in Formula Challenges - 46 comments

Time for a quick formula finesse check. Let’s say you have a number in A1. What formula can you use to find out if it has duplicate digits.

For example, if A1 has 123405, then answer should be FALSE
and if A1 has 123455, then answer should be TRUE

Go ahead and post your answers (formulas, VBA or M script) in the comments section.

Clues and hints

Check out below formula tips to get some clues on how to solve this problem.

More home work:

Why not indulge in some cheese, wine and Excel? That is how I like to enjoy my Tuesdays. Take up these homework problems to work out your formula muscles.

 

 

Written by Chandoo
Tags: , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

46 Responses to “Find out if a number has repetitive digits [formula homework]”

  1. Daniel Ferry says:

    =ISNUMBER(MODE(--MID(A1,ROW(OFFSET(A1,,,LEN(A1))),1)))

  2. Black Moses says:

    My solution isn't very elegant, but it works:

    =IF(ISERROR(SUM(1*SUBSTITUTE(A1,ROW($A$1:$A$10)-1,"x",2))),TRUE,FALSE)

    Entered as CSE of course

  3. Hossat says:

    for french users :
    =ESTNUM(MODE(--STXT(A11;LIGNE(DECALER($A$1;;;NBCAR(A11)));1)))

  4. Bob says:

    =(MID(A38,LEN(A38)-1,1)-RIGHT(A38,1))=0

  5. RingBinder says:

    {=LEN(A1)>MIN(LEN(SUBSTITUTE(A1,REPT(ROW($A$1:$A$10)-1,2),"")))}

    CSE formula

  6. David Hager says:

    Repetitive digits is different than duplicate digits. For example, 123454 would have duplicate digits whereas 123445 would have repetitive digits. Which did you want?

  7. Tom Smith says:

    The easiest way for numbers would be using SUBSTITUTION calculations, such as:

    =IF(LEN(B2)-LEN(SUBSTITUTE(B2,"0",""))>1,TRUE,IF(LEN(B2)-LEN(SUBSTITUTE(B2,"1",""))>1,TRUE,IF(LEN(B2)-LEN(SUBSTITUTE(B2,"2",""))>1,TRUE,IF(LEN(B2)-LEN(SUBSTITUTE(B2,"3",""))>1,TRUE,IF(LEN(B2)-LEN(SUBSTITUTE(B2,"4",""))>1,TRUE,IF(LEN(B2)-LEN(SUBSTITUTE(B2,"5",""))>1,TRUE,IF(LEN(B2)-LEN(SUBSTITUTE(B2,"6",""))>1,TRUE,IF(LEN(B2)-LEN(SUBSTITUTE(B2,"7",""))>1,TRUE,
    IF(LEN(B2)-LEN(SUBSTITUTE(B2,"8",""))>1,TRUE,IF(LEN(B2)-LEN(SUBSTITUTE(B2,"9",""))>1,TRUE,FALSE))))))))))

    If you want to know which digit had the most, you could simply use the logic portion comparing each number:

    =LEN(B2)-LEN(SUBSTITUTE(B2,"5",""))

    This way it doesn't matter where in the string the duplication of digits occurs, they all will be "counted."

  8. Dave Ramage says:

    Similar to Tom's solution, but array entered to save some typing!

    =(LEN(A1)-MIN(LEN(SUBSTITUTE(A1,ROW($A$1:$A$10)-1,""))))<2

    Cheers,
    Dave

    • Tonosam says:

      Great! May be you coud use {0,1,2,3,4,5,6,7,8,9} instead ROW() for clarity but it's a great solution anyway.

      Thank you for sharing!

  9. Steve says:

    Similar to Tom Smith's solution, just run through all the cases:
    =0<IFERROR(SEARCH("00",B1),0)+IFERROR(SEARCH("11",B1),0)+IFERROR(SEARCH("22",B1),0)+IFERROR(SEARCH("33",B1),0)+IFERROR(SEARCH("44",B1),0)+IFERROR(SEARCH("55",B1),0)+IFERROR(SEARCH("66",B1),0)+IFERROR(SEARCH("77",B1),0)+IFERROR(SEARCH("88",B1),0)+IFERROR(SEARCH("99",B1),0)

    As I'm ready to post, it appears this is not what you wanted. Oh well, it may be useful to someone in the future. I know I've found many solutions in the Chandoo comments section.

    • UniMord says:

      A simpler solution, but, along the same lines:
      =COUNT(SEARCH({"00","11","22","33","44","55","66","77","88","99"}, A1))>0

      • UniMord says:

        Note: Only the "00" needs to be a string - otherwise, it'll turn into a single 0. All the others can be numbers, but, it seemed sloppy to me to have an array of one string and 9 numbers.

  10. Faseeh says:

    =IF(MOD(MID(A1,LEN(A1)-1,2),11)=0,"Duplicate Digits","")

    • Greg G says:

      This only works if duplicate (repeated) numbers are last 2 digits of the number, ie. 12345677, due to MID(A1, LEN(A1)-1, 2). If the dupicates are the first numbers (112345) or not repeated side by side (123145, note the 1's) then the formula will return the FALSE result.

  11. Faseeh says:

    This one too..

    =IF(MOD(D3-ROUNDDOWN(D3,-2),11)=0,"Duplicate","")

  12. UniMord says:

    {=IFNA(MATCH(0, MOD(MID(A1&"|",ROW(INDIRECT("1:"&LEN(A1)+1)),1)&MID("|"&A1,ROW(INDIRECT("1:"&LEN(A1)+1)),1),11), 0)>0, FALSE)}

  13. UniMord says:

    To determine if there are any duplicates, consecutive or otherwise:

    =MAX(FREQUENCY(--MID(A1,ROW(INDIRECT("1:" & LEN(A1))), 1), {0,1,2,3,4,5,6,7,8,9}))>1

  14. Bill Szysz says:

    For text and numbers - my proposal (CSE formula)
    =OR(FREQUENCY(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))>1)

  15. Bill Szysz says:

    I do not know why my formula was cutted in my previous post, so this is next try
    =OR(FREQUENCY(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),
    CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))>1)

  16. Bill Szysz says:

    Here is a code for PQ
    let
    Source = Excel.CurrentWorkbook(){[Name="tblNumbers"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TextOrNumbers", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Result", each List.Distinct(Text.ToList([TextOrNumbers])) Text.ToList([TextOrNumbers]) )
    in
    #"Added Custom"

  17. Bill Szysz says:

    hmm... this site cut some part of the code so this is challenge for readers to put in the last step in correct place two signs :-))

  18. Alok Kumar Jena says:

    Hi Team,

    Find the repeated number from cell either true or false by this formula.

    =COUNT(SEARCH(REPT({1,2,3,4,5,6,7,8,9},2),A2))>0

  19. Asheesh says:

    One more for the sake of fun

    Solution for duplicates or consecutive or otherwise

    LEN(A1)>COUNT(IFERROR(SEARCH(ROW(A1:A10)-1,A1),""))

  20. Rishi Kumar says:

    Use below function in Cell b2 and put the number in cell A2.

    =MODE(--MID(A2,ROW(OFFSET($A$1,,,LEN(A2))),1))

  21. Rishi Kumar says:

    =IFERROR(MODE(--MID(A2,ROW(OFFSET($A$1,,,LEN(A2))),1)),"")

  22. John Jairo V says:

    Your solution can avoid IFERROR:

    With CSE:
    =LEN(A1)>COUNT(SEARCH(ROW(1:10)-1,A1))

    Without CSE:
    =LEN(A1)>COUNT(INDEX(SEARCH(ROW(1:10)-1,A1),))

    Blessings!

  23. Sunny says:

    =LEN(A1) NE COUNT(SEARCH({"0","1","2","3","4","5","6","7","8","9"}, A1))

  24. David N says:

    =SUMPRODUCT(--(FREQUENCY(VALUE(MID(A1,ROW($A$1:INDEX(A:A,LEN(A1))),1)),{0,1,2,3,4,5,6,7,8,9})>1))

  25. Venky says:

    Thanks John, this is clever

  26. Vipul Patel says:

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Position" = Table.ExpandListColumn(Table.TransformColumns(Table.TransformColumnTypes(Source, {{"Column1", type text}}, "en-IN"), {{"Column1", Splitter.SplitTextByRepeatedLengths(1), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
    #"Grouped Rows" = Table.Group(#"Split Column by Position", {"Column1"}, {{"Count", each Table.RowCount(_)>1, type number}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Grouped Rows",{{"Count", type text}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if [Count] = "false" then "0" else "1" ),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Conditional Column",{{"Custom", Int64.Type}}),
    #"Calculated Sum" = List.Sum(#"Changed Type1"[Custom])
    in
    #"Calculated Sum"

  27. lori says:

    another variation (non-cse / non-volatile):

    =LEN(A1)>COUNT(FIND({0,1,2,3,4}+{0;5},A1))

  28. Tanuja says:

    This was really very helpful! I got my answer!

  29. Black Moses says:

    It's interesting how many responses this article got compared to the ones immediately before and after it. It's not a perfect comparison; other articles perhaps do not invite responses in the same way. Regardless, I would surmise that people enjoy these Excel challenges.

    • Hui... says:

      @Black Moses

      I have never been able to work out why some posts get huge responses and others get very few.

      Posts I have made that I thought were fantastic have been lucky to gets comments, but others seemingly of lesser standard get inundated

      Go figure?

      Any ideas as to what makes one post more appealing than any others will be appreciated

      • Black Moses says:

        I guess I'm as guilty as anyone of reading but not commenting,

        I, for one, find your formula forensics series incredibly interesting and useful, and your latest post on conditional formatting on chart data labels is a cool trick that I hope I will have opportunity to use, but I rarely comment on articles unless there's some direct/indirect question posed (doesn't have to be a challenge, but just something to respond to), that's pretty much the key for engaging me.

        I *could* simply post "that's awesome, thanks for posting this" when I read something on here I like, which I'm sure would be welcome, but in actual fact wouldn't do much for advancing meaningful, insightful discourse on the site. And actually me posting "that's awesome" could simply be replicated with a 'like' button a la Facebook.

        I have no idea what makes some posts more appealing than others, maybe a 'like' button, or a 'How appealing did you find this post on a scale of 1-10?' web form straw poll on every article would generate some insight.

    • Chandoo says:

      It is surprising, but at least it is consistent (ie we get more responses for homework / challenge type questions). Also, over the years I think blog has gone quieter (despite having way more traffic than earlier times). It does feel like an echo chamber sometimes and I am not sure if a certain topic / article is useful to the audience or not. But we march along, one post at a time.

  30. Shweta Jain says:

    =IF(SUM((FREQUENCY(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1,MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1)>1)*1)=1,TRUE,FALSE)

  31. James says:

    for the fun of it one more
    {=SUM(IFERROR(SEARCH({0,1,2,3,4,5,6,7,8,9},A1),0))=LEN(A1)*(LEN(A1)+1)/2}

Leave a Reply