=IF(ISNUMBER(SEARCH("*4*",A1)),"do this","do that")
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 ]
=IF(ISNUMBER(SEARCH("*4*",A1)),"do this","do that")
could you explain at little further: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
The field may contain several references seperated ie "2;3;4;5;6;7"
which field? A1 etc. or the IF() criteria?
"Have a good time......all the time"
Ian Mac
Hi swaink: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 ]
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
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
Hi Mark:On 2002-04-04 08:16, Mark W. wrote:
=IF(ISNUMBER(SEARCH("*4*",A1)),"do this","do that")
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
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"......One question, why the leading and trailing * -- your formula does work without the * also. TIA
=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 ]
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 ]
Excellent!!!On 2002-04-04 09:22, Mark W. wrote:
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"......One question, why the leading and trailing * -- your formula does work without the * also. TIA
=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 ]
"Have a good time......all the time"
Ian Mac
Like this thread? Share it with others