Search for Text within a Formula Result Q

[Sean]

I have the following formula in Range A8:A30-

=IF('Mix Check'!U8="","",'Mix Check'!U8&'Mix Check'!N8&'Mix Check'!
O8&'Mix Check'!P8&'Mix Check'!Q8&'Mix Check'!R8&'Mix Check'!S8&'Mix
Check'!T8)

This will produce a result something like: - '1 Day Sales Mix missing
Mon; Tue' etc etc

How can I check in AA1; if my value that is held within A1 (this will
show a value of one of Mon, Tue, Wed, Thur, Fri, Sat, Sun), appears
anywhere within the result in A8; A11;A14, If it does appear, return
0, if it doesn't Return 0?

I have different non-contiguous range I wish to check hence A8; A11;
A14 etc

Thanks

 

[Ron Rosenfeld]

I have the following formula in Range A8:A30-

=IF('Mix Check'!U8="","",'Mix Check'!U8&'Mix Check'!N8&'Mix Check'!
O8&'Mix Check'!P8&'Mix Check'!Q8&'Mix Check'!R8&'Mix Check'!S8&'Mix
Check'!T8)

This will produce a result something like: - '1 Day Sales Mix missing
Mon; Tue' etc etc

How can I check in AA1; if my value that is held within A1 (this will
show a value of one of Mon, Tue, Wed, Thur, Fri, Sat, Sun), appears
anywhere within the result in A8; A11;A14, If it does appear, return
0, if it doesn't Return 0?

I assume the above is a typo, otherwise you could just enter

A8: 0

I have different non-contiguous range I wish to check hence A8; A11;
A14 etc

Thanks

To check AA1, and return 1 if present and 0 if not present:

=--OR(NOT(ISERR(SEARCH({"Sun","Mon","Tue","Wed","Thu","Fri","Sat"},AA1))))

To check a different cell, change the cell reference.

To check to see if this value might be present in any of a series of
non-contiguous (or contiguous) cells, one method, short of using VBA, would be
to concatenate replace the within_text argument with a concatenation of the
range:

=--OR(NOT(ISERR(SEARCH({"Sun","Mon","Tue","Wed","Thu","Fri","Sat"},
CONCATENATE(AA1,AA3,AA5,W2)))))

If you have more than 29 cells, then, instead of using the CONCATENATE
worksheet function, you will have to use the "&" operator, or a combination of
the two.
--ron

 

[Sean]

Thanks Ron, yes indeed it was a typo should have been "...If it does
appear, return 0, if it doesn't Return 1"

If the exact text I am loking for is in AA1 and I am looking for this
in the Ranges AA3, AA5, how exactly would this formula work

=--OR(NOT(ISERR(SEARCH({"Sun","Mon","Tue","Wed","Thu","Fri","Sat"},
CONCATENATE(AA1,AA3,AA5,W2)))))

I should have said that the value in AA1 will change each day, so
tomorrow I will be looking for the text "Thur". I'm not looking for
ANY of "Sun","Mon","Tue","Wed","Thu","Fri","Sat", within the range
AA3, AA5, just specifically what is returned in AA1 (result in AA1 is
a value not a formula)

 

[Ron Rosenfeld]

Thanks Ron, yes indeed it was a typo should have been "...If it does
appear, return 0, if it doesn't Return 1"

Then we need to remove the NOT

If the exact text I am loking for is in AA1 and I am looking for this
in the Ranges AA3, AA5, how exactly would this formula work

=--OR(NOT(ISERR(SEARCH({"Sun","Mon","Tue","Wed","Thu","Fri","Sat"},
CONCATENATE(AA1,AA3,AA5,W2)))))

I should have said that the value in AA1 will change each day, so
tomorrow I will be looking for the text "Thur". I'm not looking for
ANY of "Sun","Mon","Tue","Wed","Thu","Fri","Sat", within the range
AA3, AA5, just specifically what is returned in AA1 (result in AA1 is
a value not a formula)

=--ISERR(SEARCH(AA1,CONCATENATE(AA3,AA5)))


--ron

 

[Sean]

Ron many thanks for your help, works like a dream