Repeated Digits in a CELL

[hrayani]

Dear All,

I am using this formula for repeated numbers in a range

=IF(COUNTIF(A1:I1,0)>1,"0","")&SUBSTITUTE(SUM((COUNTIF(A1:I1,{1,2,3,4,5,6,7,8,9})>1)*{1,2,3,4,5,6,7,8,9}*10^{8,7,6,5,4,3,2,1,0}),0,"")

i need a formula for Repeated Digits in a cell

Regards,

Humayun

 

[repairman615]

To make sure I understand,

If a cell contains: 23445
The result would be: 4

Or if: 22556
would be: 25

Is that correct?

 

[hrayani]

yes exactly

 

[repairman615]

This may be a start,

=IFERROR(MID(A1,FIND(MID(A1,1,1),A1,2),1),)&IFERROR(MID(A1,FIND(MID(A1,2,1),A1,3),1),)&IFERROR(MID(A1,FIND(MID(A1,3,1),A1,4),1),)&IFERROR(MID(A1,FIND(MID(A1,4,1),A1,5),1),)&IFERROR(MID(A1,FIND(MID(A1,5,1),A1,6),1),)


I do think it may have a few problems. This handles a 5 digit number, a number like:
22245
will result in:
22

Please test to see what you think.

Cell is A1

 

[hrayani]

sorry dear

its not working

its showing #NAME ...............

 

[repairman615]

Are you using 2003?

If so, the Iferror function is not available as this was added in 2007, which is what I am using.


I will use the iserr function and repost.

 

[hrayani]

yes JEFF

i am using 2003

 

[repairman615]

=IF(ISERR(MID(A1,FIND(MID(A1,1,1),A1,2),1)),,MID(A1,FIND(MID(A1,1,1),A1,2),1))&IF(ISERR(MID(A1,FIND(MID(A1,2,1),A1,3),1)),,MID(A1,FIND(MID(A1,2,1),A1,3),1))&IF(ISERR(MID(A1,FIND(MID(A1,3,1),A1,4),1)),,MID(A1,FIND(MID(A1,3,1),A1,4),1))&IF(ISERR(MID(A1,FIND(MID(A1,4,1),A1,5),1)),,MID(A1,FIND(MID(A1,4,1),A1,5),1))&IF(ISERR(MID(A1,FIND(MID(A1,5,1),A1,6),1)),,MID(A1,FIND(MID(A1,5,1),A1,6),1))

cell is A1

 

[hrayani]

Its working now

cell a1 = 22245

but you are right that the result is 22 whereas, it should be only 2

any idea what to do now?

 

[pgc01]

Hi Humayun

Try also:

=IF(ISNUMBER(FIND(0,A1)),"0","")&SUBSTITUTE(SUMPRODUCT(ISNUMBER(FIND({1,2,3,4,5,6,7,8,9},A1))*{1,2,3,4,5,6,7,8,9}*10^{8,7,6,5,4,3,2,1,0}),0,"")