(1) Missing Digits In A CELL (2) Repeated Digits In A CELL (3) Unique Digits In A CELL

[hrayani]

Hello To Everyone,

I have been working on the formulas of repeated, unique and missing digits in a cell and have solved all the issues with the help of

1) Sachin
2) PGC01
3) Repairman615 (dear friend)

Below i am posting all the formulas step by step so that everyone who requires these formulas can get help from it...

POINT # 1 ( FORMULA FOR > Missing Digits In A Cell)

=IF(ISERR(FIND(0,A1,1)),0,"")&IF(ISERR(FIND(1,A1,1)),1,"")&IF(ISERR(FIND(2,A1,1)),2,"")&IF(ISERR(FIND(3,A1,1)),3,"")&IF(ISERR(FIND(4,A1,1)),4,"")&IF(ISERR(FIND(5,A1,1)),5,"")&IF(ISERR(FIND(6,A1,1)),6,"")&IF(ISERR(FIND(7,A1,1)),7,"")&IF(ISERR(FIND(8,A1,1)),8,"")&IF(ISERR(FIND(9,A1,1)),9,"")

Cell A1 = 23788 > Formula Answer = 014569

POINT # 2 ( FORMULA FOR > Repeated Digits In A Cell)

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

Cell A2 = 23788 > Formula Answer = 8


POINT # 3 ( FORMULA FOR > Unique Digits In A Cell)

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

Cell A3 = 23788 > Formula Answer = 237


Once again thanks to all those who helped me

Best Regards,

Humayun Rayani

 

[hrayani]

Repost With All The Formulas Of Same Kind

Hello To Everyone,

I have been working on the formulas of repeated, unique and missing digits in a cell and have solved all the issues with the help of

1) Sachin
2) PGC01
3) Repairman615 (dear friend)

Below i am posting all the formulas step by step so that everyone who requires these formulas can get help from it...

POINT # 1 ( FORMULA FOR > Missing Digits In A Cell)

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

Cell A1 = 23788 > Formula Answer = 014569

POINT # 2 ( FORMULA FOR > Repeated Digits In A Cell)

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

Cell A2 = 23788 > Formula Answer = 8

POINT # 3 ( FORMULA FOR > Unique Digits In A Cell)

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

Cell A3 = 23788 > Formula Answer = 237


Once again thanks to all those who helped me

Best Regards,

Humayun Rayani
<!-- / message --><!-- edit note -->