missing digits

[durisdeer]

Hi,

I want to find which specific digits (in the range 1-9) are missing from a cell: eg:

A1 = 112953429746

needs to return "8", as it's the only digit missing. any ideas?

many thanks, Robbie

[MrShorty]

You don't give any specific details regarding your requirements, and I didn't spend a lot of time on it, as I don't have a practical use for such an algorithm, but here's how I did this:

A1=number
B1=TEXT(a1,0) [convert number to text]

D1 through D10= digits 1-0 entered as text
E1 = ISERR(FIND(d1,$b$1)) copied down through E10. [returns a list of TRUE/FALSE. TRUE means the corresponding digit in column D was not found in $B$1.]
F1=IF(e1,d1,"") [gives a broken up column with the digits that weren't found in B1]
G1=COUNTIF(e1:e10,true) [indicates how many digits were left out

Note that I included the digit 0 in my search. If you know the digit 0 will never be present in your numbers, then it doesn't matter.

To kind of clean up the sheet, I hid columns B:E, so all I see is the original input number and the list of missing digits.

That should get you started.

[Domenic]

Try the following...

B1:

=9-SUMPRODUCT(--(ISNUMBER(SEARCH({1,2,3,4,5,6,7,8,9},$A1))))

C1, copied across:

=IF(COLUMN()-COLUMN($C1)+1<=$B1,INDEX({1,2,3,4,5,6,7,8,9},SMALL(IF(1-ISNUMBER(SEARCH({1,2,3,4,5,6,7,8,9},$A1)),{1,2,3,4,5,6,7,8,9}),COLUMNS($C1:C1))),"")

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER. If you have a column of numbers, copy the formula for B1 down the column. Also, copy the formula for C1 across and down.

Hope this helps!

[durisdeer]

Thanks a lot to both of you. I'm going about the problem a slightly different way now (it's a sudoko solver), but I'm learning a lot of new and useful stuff from these replies..

Thanks again,

Robbie

[olasa]

And one more option...
Ola Sandström