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

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

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. Hello Awesome...

My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.

Thank you and see you around.

### Related articles:

 Written by Chandoo Tags: duplicates, homework, Microsoft Excel Formulas, unique Home: Chandoo.org Main Page ? Doubt: Ask an Excel Question

### 53 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

• Asheesh says:

Hi,

There is no need to use IF function in the proposed solution..

ISERROR(SUM(1*SUBSTITUTE(A1,ROW(\$A\$1:\$A\$10)-1,"x",2)))

• Black Moses says:

Haha you're right of course, thanks

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?

• Chandoo says:

Both.

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.

• Faseeh says:

The example by chandoo stated only last two digits!

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}}),
in

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

• NItin says:

Its not a give correct Output when you apply this function on when duplicate number not in consecutive seq.
For Example:-102565

• Alok Kumar Jena says:

It's asking about repetitive number means continue like (12344) not like 102565.

• lori says:

that does consecutive digits, a small tweak might do the trick:

=COUNT(SEARCH(REPT({0,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:

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:

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}

32. trainsauce says:

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

33. Maxwell Deutsch says:

=AND(ISERR(FIND("00",TEXT(A1,"0"))),ISERR(FIND("11",TEXT(A1,"0"))),ISERR(FIND("22",TEXT(A1,"0"))),ISERR(FIND("33",TEXT(A1,"0"))),ISERR(FIND("44",TEXT(A1,"0"))),ISERR(FIND("55",TEXT(A1,"0"))),ISERR(FIND("66",TEXT(A1,"0"))),ISERR(FIND("77",TEXT(A1,"0"))),ISERR(FIND("88",TEXT(A1,"0"))),ISERR(FIND("99",TEXT(A1,"0"))))

I challenged myself to come up with any solution before looking through the comments. I'm blown away by how much I can learn from this post alone. I knew there'd be an easier way out there.

34. sam says:

=COUNT(FIND((ROW(1:10)-1),A1))=LEN(A1)

35. Kutty says:

IF(IFERROR(FIND(9,A1)<FIND(9,A1,FIND(9,A1)+1),FALSE)=TRUE,TRUE,IF(IFERROR(FIND(8,A1)<FIND(8,A1,FIND(8,A1)+1),FALSE)=TRUE,TRUE,IF(IFERROR(FIND(7,A1)<FIND(7,A1,FIND(7,A1)+1),FALSE)=TRUE,TRUE,IF(IFERROR(FIND(6,A1)<FIND(6,A1,FIND(6,A1)+1),FALSE)=TRUE,TRUE,IF(IFERROR(FIND(5,A1)<FIND(5,A1,FIND(5,A1)+1),FALSE)=TRUE,TRUE,IF(IFERROR(FIND(4,A1)<FIND(4,A1,FIND(4,A1)+1),FALSE)=TRUE,TRUE,IF(IFERROR(FIND(3,A1)<FIND(3,A1,FIND(3,A1)+1),FALSE)=TRUE,TRUE,IF(IFERROR(FIND(2,A1)<FIND(2,A1,FIND(2,A1)+1),FALSE)=TRUE,TRUE,IF(IFERROR(FIND(1,A1)<FIND(1,A1,FIND(1,A1)+1),FALSE)=TRUE,TRUE,IF(IFERROR(FIND(0,A1)<FIND(0,A1,FIND(0,A1)+1),FALSE)=TRUE,TRUE,FALSE))))))))))

it's too long formula but it's perfect formula for within the cell conditional

36. Paul says:

=SUMPRODUCT(--(MID(A1,ROW(INDIRECT("1:"&LEN(A1)-1)),1)=
MID(A1,ROW(INDIRECT("2:"&LEN(A1))),1)))>0

37. PipO says:

Maybe late ...
=IF(ISNUMBER(AGGREGATE(15,6,FIND(SEQUENCE(10,,0)&SEQUENCE(10,,0),B5),1)),"Yes","No")
starting from B5

 « D’oh – Visualizing Homer’s favorite sayings in Power BI Histograms & Pareto charts in Excel – tutorial, tips and downloadable template »

### Get FREE Excel & Power-BI Newsletter

One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free.