Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search for Text within a Formula Result Q
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search for Text within a Formula Result Q
On Thu, 14 Feb 2008 04:23:36 -0800 (PST), Sean wrote:
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search for Text within a Formula Result Q
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) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search for Text within a Formula Result Q
On Thu, 14 Feb 2008 05:58:25 -0800 (PST), Sean wrote:
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search for Text within a Formula Result Q
Ron many thanks for your help, works like a dream
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search for Text within a Formula Result Q
On Thu, 14 Feb 2008 06:51:00 -0800 (PST), Sean wrote:
Ron many thanks for your help, works like a dream Excellent. Thanks for the feedback. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Text Result Formula | Excel Discussion (Misc queries) | |||
Formatting text which is result of formula | Excel Discussion (Misc queries) | |||
what if the result of a formula is text | Excel Worksheet Functions | |||
formula is displayed as literal text instead of formula result | Excel Discussion (Misc queries) | |||
center text in a formula result | Excel Worksheet Functions |