Check for NON-presence of a string in another string
 

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

Check for NON-presence of a string in another string
 

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

On Dec 11, 10:58*am, "G.P.N.L. c.v.a." wrote:
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

Check for NON-presence of a string in another string
 

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

Arvi Laanemets


"G.P.N.L. c.v.a." wrote in message
...
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

Check for NON-presence of a string in another string
 

G.P.N.L. c.v.a.;583982 Wrote:
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

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


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: 558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=161656

Microsoft Office Help

Check for NON-presence of a string in another string
 

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.

--
Rick (MVP - Excel)


"G.P.N.L. c.v.a." wrote in message
...
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