Excel search function

[Thyag]

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.

 

[Leo Rod]

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?

 

[CLR]

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

 

[Dave Peterson]

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)

 

[Rick Rothstein \(MVP - VB\)]

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

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)

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

 

[Dave Peterson]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Thyag]

Thanks to Every body's support.

Thanks,
Thyag