if statement

[swaink]

Hi All Does anyone know how I may do this.

I need to be able to search a field that will contain a text value.

I want to use the IF statement "If A1 contains "4" then do this"

The field may contain several references seperated ie "2;3;4;5;6;7" or "1;4;7"

Any help would be appreciated

Kevin

[ This Message was edited by: swaink on 2002-04-04 08:15 ]

[Mark W.]

=IF(ISNUMBER(SEARCH("*4*",A1)),"do this","do that")

[Ian Mac]

On 2002-04-04 08:06, swaink wrote:
Hi All Does anyone know how I may do this.

I need to be able to search a field that will contain a text value.

I want to use the IF statement "If A1 contains "4" then do this"

The field may contain several references seperated ie "2;3;4;5;6;7"

Any help would be appreciated

Kevin

could you explain at little further:

The field may contain several references seperated ie "2;3;4;5;6;7"

which field? A1 etc. or the IF() criteria?

[Yogi Anand]

On 2002-04-04 08:06, swaink wrote:
Hi All Does anyone know how I may do this.

I need to be able to search a field that will contain a text value.

I want to use the IF statement "If A1 contains "4" then do this"

The field may contain several references seperated ie "2;3;4;5;6;7" or "1;4;7"

Any help would be appreciated

Kevin

[ This Message was edited by: swaink on 2002-04-04 08:15 ]

Hi swaink:
This should get you started ... then you can enhance it to do some further error checking:

in cell C5 2;3;4;5

=IF(FIND("4",C5,1),"Found It","no 4 here")

with the result

Found It


Please post back if it works for you ... otherwise explain a little further and let us take it from there

[swaink]

Mark, Ian

Many thanks for your response. Mark that works a treat. And Ian It was the field that may contain the text value, sorry if i confused you.

Thanks again

Kevin

[Yogi Anand]

On 2002-04-04 08:16, Mark W. wrote:
=IF(ISNUMBER(SEARCH("*4*",A1)),"do this","do that")

Hi Mark:
That's neat ... looks better than mine with the find and act approach. One question, why the leading a nd trailing * -- your formula does work without the * also. TIA

[Mark W.]

...One question, why the leading and trailing * -- your formula does work without the * also. TIA

I did it out of habit. Since I didn't care about the position of 4 in the string, "*4*" always returns 1 if 4 is present. This allows a summary count of "conditional flags"...

=SUM(ISNUMBER(SEARCH("*4*",A1)),0,1,1)

Also it's easy to change into an AND condition...

=IF(ISNUMBER(SEARCH("*4*7*",A1)),"do this","do that")

...returns "do this" if an ordered list (implemented as a text string) contains both 4 and 7.


[ This Message was edited by: Mark W. on 2002-04-04 09:22 ]

[Yogi Anand]

Thanks Mark!
I did expect to hear some thing along this line.

[ This Message was edited by: Yogi Anand on 2002-04-04 09:25 ]

[Ian Mac]

On 2002-04-04 09:22, Mark W. wrote:

...One question, why the leading and trailing * -- your formula does work without the * also. TIA

I did it out of habit. Since I didn't care about the position of 4 in the string, "*4*" always returns 1 if 4 is present. This allows a summary count of "conditional flags"...

=SUM(ISNUMBER(SEARCH("*4*",A1)),0,1,1)

Also it's easy to change into an AND condition...

=IF(ISNUMBER(SEARCH("*4*7*",A1)),"do this","do that")

...returns "do this" if an ordered list (implemented as a text string) contains both 4 and 7.


[ This Message was edited by: Mark W. on 2002-04-04 09:22 ]

Excellent!!!