Check for NON-presence of a string in another string

[G.P.N.L. c.v.a.]

I am looking for a way to find out whether a certain string does NOT appear
in another string.
In that case FIND will give an error.

I tried to bypass this by using NOT(ISERROR(...), but this doen't seem to
work.

I tried using NOT(ISERROR(FIND("10";$BG20;1)<>0 to check if "10" is prsent
in BG20
in this formula :
=IF(AND( condition 1; condition 2);
IF( $BG$1="M"; $BG20;
IF( AND($BG$1="K";
NOT(ISERROR(FIND("10";$BG20;1))));
$BG20;
IF( AND($BG$1="J";
NOT(ISERROR(FIND("10";$BG20;1))));
$BG20)));
""
)
but for BG1 = "M", I still get "FALSE" instaed of an empty field.

What am I doing wrong ?
Tx in advance,
Gilbert

 

[Pete_UK]

Instead of NOT(ISERROR(FIND(... you can use ISNUMBER(FIND(...

Check that BG1 only contains "M" and not "M " (i.e extra spaces in
there).

You wont actually get an empty field returned if BG1 does equal "M",
as your formula will return BG20. If BG20 is blank then your formula
will return 0.

Hope this helps.

Pete

 

[Arvi Laanemets]

=(LEN(SUBSTITUTE(A1,"xxx",""))=LEN(A1))

Arvi Laanemets

 

[p45cal]

I am looking for a way to find out whether a certain string does NO
appear
in another string.
In that case FIND will give an error.

I tried to bypass this by using NOT(ISERROR(...), but this doen't see
to
work.

I tried using NOT(ISERROR(FIND("10";$BG20;1)<>0 to check if "10" i
prsent
in BG20
in this formula :
=IF(AND( condition 1; condition 2);
IF( $BG$1="M"; $BG20;
IF( AND($BG$1="K";
NOT(ISERROR(FIND("10";$BG20;1))));
$BG20;
IF( AND($BG$1="J";
NOT(ISERROR(FIND("10";$BG20;1))));
$BG20)));
""
)
but for BG1 = "M", I still get "FALSE" instaed of an empty field.

What am I doing wrong ?
Tx in advance,
Gilbert

Try losing the '<>0':
=NOT(ISERROR(FIND("10",$BG20,1)))
or rather
=NOT(ISERROR(FIND("10";$BG20;1)))
Works here

 

[Rick Rothstein]

You can use this test in your IF function calls...

COUNTIF(BG20,"*10*")>0

which will report TRUE if 10 is contained in BG20 and FALSE if it is not.