Rick Rothstein (MVP - VB) said:
Try this (note, there is no 0 in the list)...
=SEARCH({1,2,3,4,5,6,7,8.9},A1)
Note the typo 8.9 rather than 8,9.
The corrected formula would return the array
{1,4,#VALUE!,6,5,#VALUE!,#VALUE!,#VALUE!}
Note, however, that A1 contains 5 nonzero numerals, but the formula returns
only 4 positions - it misses the position of the 2nd '1'. If all these
positions were needed, one way would be using the defined names
nonzero: ={1,2,3,4,5,6,7,8,9}&""
seq: ={1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16}
then the formula
=IF(MID(A1,seq,1)=nonzero,seq,0)
would return a 16-row by 9-column array result. If the ith character in A1
were a nonzero numeral, the ith row of this array would have i in the column
corresponding to that numeral's position in the array named nonzero and "-"
in all other columns.
For example, if A1 were "620259", the array result would be
0 0 0 0 0 1 0 0 0
0 2 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0
0 4 0 0 0 0 0 0 0
0 0 0 0 5 0 0 0 0
0 0 0 0 0 0 0 0 6
0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0
The position of the jth nonzero numberal could be found using the array
formula
=SMALL(IF(MMULT(IF(MID(A1,seq,1)=nonzero,seq,0),
TRANSPOSE(nonzero)^0)>0,seq),j)
and checking whether the kth character in A1 were a nonzero numeral could be
done using
=COUNT(MATCH(MID(A1,5,1),nonzero,0))>0
There are probably better, more compact ways to do what the OP actually
wants to do, but for the general case array processing is unavoidable.
