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]

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,"")

I think this is fantastic!
Very Nice.

 

[hrayani]

Dear Pgc01

First of all Thanks for your reply

secondaly, dear what i require is a result which shows only repeated digits in a cell - whereas, the formula which you just posted is not matching the requirement

i have entered 56225 in CELL A1
the formula result should be 25

whereas, the result which i am currrently getting from your formula is 256

Awaiting reply,

Humayun

 

[pgc01]

Sorry, you are right, I did not read your post with attention and my formula is showing all the digits in A1, not just the repeated ones.

To get only the repeated ones, try, for ex.:

=IF(ISNUMBER(SEARCH(0&"*"&0,A1)),"0","")&SUBSTITUTE(SUMPRODUCT(ISNUMBER(SEARCH({1,2,3,4,5,6,7,8,9}&"*"&{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,"")

 

[hrayani]

Dear Pgc01

Wow - FANTASTIC..... Worked PERFECT..

Thanks a Lot..

now the last problem for a cell is left. can we just modify this formula for Unique Numbers in a CeLL..

sorry i will post a new thread in a minute for this matter so that everyone should get help from it..

Pls see my new thread in a minute..

THREAD > UNIQUE DIGITS IN A CELL

 

[pgc01]

Humayun, I'm glad it helped.

Since you want both the unique and the repeated digits, you can use another formula for the repeated ones. That makes the 2 formulas similar

Unique digits:

=IF(LEN(A1)-LEN(SUBSTITUTE(A1,0,""))=1,"0","")&SUBSTITUTE(SUMPRODUCT((LEN(A1)-LEN(SUBSTITUTE(A1,{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,"")

Repeated digits:

=IF(LEN(A1)-LEN(SUBSTITUTE(A1,0,""))>1,"0","")&SUBSTITUTE(SUMPRODUCT((LEN(A1)-LEN(SUBSTITUTE(A1,{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,"")

 

[hrayani]

Thanks PGC01

Thanks for your time and support...

It really helped me a LOT

GOD BLESS U

Regards,

Humayun Rayani

 

[hrayani]

Dear PGC01

As For the missing Digits i have just modified your formula of Unique numbers

I have changed the = sign to < less then sign

now all three formulas are the same

=IF(LEN(A1)-LEN(SUBSTITUTE(A1,0,""))<1,"0","")&SUBSTITUTE(SUMPRODUCT((LEN(A1)-LEN(SUBSTITUTE(A1,{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,"")

 

[pgc01]

Dear PGC01

As For the missing Digits i have just modified your formula of Unique numbers

I have changed the = sign to < less then sign

now all three formulas are the same

=IF(LEN(A1)-LEN(SUBSTITUTE(A1,0,""))<1,"0","")&SUBSTITUTE(SUMPRODUCT((LEN(A1)-LEN(SUBSTITUTE(A1,{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,"")

Yes, it's a nice solution. All the formulas similar.

Cheers!