Excel search function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Thyag - 10 Aug 2007 16:03 GMT

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.

Reply to this Message

Leo Rod - 10 Aug 2007 16:31 GMT

Try: =FIND("0",A1)
If the strig of values have a zero this formula will return the position
inside the cell, if the strig does not have any zeroes, the formula will
return an error.

Is this what you want?

> Hi All,
> Is there a way to use the search function to search non "0" value in
[quoted text clipped - 4 lines]
>
> Thanks, Thyag.

Reply to this Message

CLR - 10 Aug 2007 16:46 GMT

Assuming you always have a six character string.....this formula will show
the position of any zero therein.

=IF(LEFT(A1,1)="0","_",1)&IF(MID(A1,2,1)="0","_",2)&IF(MID(A1,3,1)="0","_",3)&IF(MID(A1,4,1)="0","_",4)&IF(MID(A1,5,1)="0","_",5)&IF(MID(A1,6,1)="0","_",6)

Vaya con Dios,
Chuck, CABGx3

> Hi All,
> Is there a way to use the search function to search non "0" value in
[quoted text clipped - 4 lines]
>
> Thanks, Thyag.

Reply to this Message

Dave Peterson - 10 Aug 2007 17:40 GMT

If the value is an integer:
=left(a1,1)

if the value is a string (and could have leading 0's):
=left(substitute(a1,"0",""),1)

> Hi All,
> Is there a way to use the search function to search non "0" value in
[quoted text clipped - 4 lines]
>
> Thanks, Thyag.

Signature

Dave Peterson

Reply to this Message

Rick Rothstein (MVP - VB) - 11 Aug 2007 20:00 GMT

>> Is there a way to use the search function to search non "0" value in
>> cell.
[quoted text clipped - 7 lines]
> if the value is a string (and could have leading 0's):
> =left(substitute(a1,"0",""),1)

I'm thinking the latter case is what the OP has since he asked for "the
position of non zero digit". That means we need to extend your function to
return the position number itself, not just the digit. While the OP asked to
use SUBSTITUTE, I chose to use FIND instead as there is no upper/lower case
issues with digits. The following extention to your formula returns the
requested position number of the first non-zero digit in the string of
digits...

=FIND(LEFT(SUBSTITUTE(A1,"0",""),1),A1)

Rick

Reply to this Message

Dave Peterson - 11 Aug 2007 20:32 GMT

I still don't know what the OP really wanted--at any of his posts.

> >> Is there a way to use the search function to search non "0" value in
> >> cell.
[quoted text clipped - 19 lines]
>
> Rick

Signature

Dave Peterson

Reply to this Message

Rick Rothstein (MVP - VB) - 11 Aug 2007 21:14 GMT

>I still don't know what the OP really wanted--at any of his posts.

LOL... yeah, he hasn't really been totally clear on that, has he.

Rick

Reply to this Message

Thyag - 13 Aug 2007 14:59 GMT

Thanks to Every body's support.

Thanks,
Thyag

Reply to this Message