Hi All,

Is there a way to use the search function to search non "0" value in
cell.

Ex - if i have "110254" in cell A1 - I need the position of non zero
digit in the string of A1.


Thanks, Thyag.

Is there a way to use the search function to search non "0" value in
cell.

Ex - if i have "110254" in cell A1 - I need the position of non zero
digit in the string of A1.

Try this (note, there is no 0 in the list)...

=SEARCH({1,2,3,4,5,6,7,8.9},A1)

Rick

On Aug 10, 10:22 am, "Rick Rothstein \(MVP - VB\)"
wrote:
Is there a way to use the search function to search non "0" value in
cell.

Ex - if i have "110254" in cell A1 - I need the position of non zero
digit in the string of A1.

Try this (note, there is no 0 in the list)...

=SEARCH({1,2,3,4,5,6,7,8.9},A1)

Rick

Thank you Rick.

On Aug 10, 10:49 am, Thyag wrote:
On Aug 10, 10:22 am, "Rick Rothstein \(MVP - VB\)"

wrote:
Is there a way to use the search function to search non "0" value in
cell.

Ex - if i have "110254" in cell A1 - I need the position of non zero
digit in the string of A1.

Try this (note, there is no 0 in the list)...

=SEARCH({1,2,3,4,5,6,7,8.9},A1)

Rick

Thank you Rick.

But the formula checks only the existence of "1" and "2" to "9" is not
validated.

"Rick Rothstein (MVP - VB)" wrote...
....
Ex - if i have "110254" in cell A1 - I need the position of non zero
digit in the string of A1.

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.

You've got lots of responses to all your other posts, too.

Thyag wrote:

Hi All,

Is there a way to use the search function to search non "0" value in
cell.

Ex - if i have "110254" in cell A1 - I need the position of non zero
digit in the string of A1.

Thanks, Thyag.

--

Dave Peterson