Count number of times a specific number occurs in a string

[westend9876]

Here is what I have in A1:
X1100000000

I want to look at the first 6 characters and if the number 1 appears more than once return Yes otherwise return No.

 

[azumi]

for first 6, maybe
=LEFT(A1;6)

for 2nd formula
=IF(LEN(A1)-LEN(SUBSTITUTE(A1,1,""))>1,"Yes","No")

 

[Hui]

=IF(LEN(SUBSTITUTE(LEFT(A1,6),1,""))<5,"Yes","No")

 

[bosco_yip]

=IF(LEN(SUBSTITUTE(LEFT(A1,6),1,""))<5,"Yes","No")

If data has 5 characters e.g. X0100, the above formula will give wrong result.

My proposal is :

=IF(ISERR(FIND(1,LEFT(A1,6),FIND(1,LEFT(A1,6))+1)>1),"No","Yes")

Regards
Bosco

 

[NARAYANK991]

Hi ,

What about this ?

=IF(LEN(SUBSTITUTE(LEFT(A1 & "000000",6),1,""))<5,"Yes","No")

Narayan

 

[deciog]

westend9876

my contribution

=IFERROR(IF(FIND(1,LEFT(A1.6))>1."Yes"."No")."No")

Décio