Comparing digits in a number in an excel cell

Question

In a list of 3 digit numbers from 000 to 999 I need to be able to identify the numbers that have at least 2 digits the same, could you please help me with a formula.
I tried =IF(LEFT(A98,1) = "LEFT(A98,2)"OR LEFT(A98,2) = "LEFT(A98,3)" OR LEFT(A98,1) ="LEFT(A98,3)" , "0","1"). But I am very inexperienced at excel and it doesn't accept this formula.

Answer 1

That's actually not that far off.

Use MID() instead of LEFT(), because LEFT() takes all the letters not just one for comparison.

Remove the unnecessary quotes, and it should work:

IF(OR(MID(A98,1,1)=MID(A98,2,1),MID(A98,1,1)=MID(A98,3,1),MID(A98,2,1)=MID(A98,3,1)),"1","0")

Make sure to understand the pieces of what I wrote, so you can correct it if I mistyped or forgot a bracket - I have no computer here to verify, and I am typing on a cell phone.

Answer 2

Aganju's answer is probably what you're looking for. A lot of times, though, the solution to an Excel problem comes from looking at it in different ways. Here's a little different approach with a couple of ideas you can keep in your back pocket.

LEFT, RIGHT, MID

Since everything is three digits, I find it easier to thinks of the digits as:

LEFT(A98,1)
MID(A98,2,1)
RIGHT(A98,1)

Just personal preference, and this will document how to use those functions. That's the leftmost 1 character, the middle starting at position 2 for a length of 1, and the rightmost 1 character.

Reverse Thinking

You approached this from the direction of finding any combination of matching digits. That requires some combination of IF, OR, and/or AND. If you approach it from the other direction, there's just one condition you want to exclude -- all of the digits being different. Testing for that single case can be done more simply.

Boolean Arithmetic

I'll combine this with another trick. You're looking for a 1 or 0 result, so you're already thinking in terms of those representing true and false. You can use some math properties of 0 and 1:

• zero times anything is zero
• multiplying a bunch of zeros yields zero
• multiplying a bunch of ones yields one
• the only way the product of a bunch of zeros or ones can yield one is if they're all ones

Putting it together

Excel treats true and false as1 and 0. So without needing IF, AND, or OR, you can use:

= (LEFT(A98,1)<>MID(A98,2,1)) * (LEFT(A98,1)<>RIGHT(A98,1)) * (MID(A98,2,1)<>RIGHT(A98,1))

This yields 1 if all the digits are different and 0 if any combination is the same. That differentiates things. But if your mindset is that it should be the reverse, as you described in the question, you can reverse it by using 1 minus that formula:

= 1 - (LEFT(A98,1)<>MID(A98,2,1)) * (LEFT(A98,1)<>RIGHT(A98,1)) * (MID(A98,2,1)<>RIGHT(A98,1))

And notice that the 0 or 1 values are produced directly by the logical tests; it wasn't necessary to assign these values to the result based on evaluating conditions.

Answer 3

Here's another slightly different way, using @fixer1234's LEFT MID & RIGHT and multiplication by zero idea:

=IF((LEFT(A99,1)-MID(A99,2,1)) * (LEFT(A99,1)-RIGHT(A99,1)) * (MID(A99,2,1)-RIGHT(A99,1))=0,1,0)

if any of the pairs are equal, the result of the multiplication is zero, and the result of the IF is 1.