Test Cell For Multiple Criteria
 

Hi,

I have this rather poor formula to test whether whether N2 contains
certain text (£$450) or is blank.

=IF(ISBLANK(N2),"Missing
Data",IF(ISERROR(FIND("£",N2)),IF(ISERROR(FIND("$" ,N2)),IF(ISERROR(FIND("4",N2)),IF(ISERROR(FIND("5" ,N2)),IF(ISERROR(FIND("0",N2)),N2,"Missing
Data"),"Missing Data"),"Missing Data"),"Missing Data"),"Missing Data"))

I would like to test for these !"£$%^&*.()_+-1234567890 (and to test
for a blank) and if the cell contains any of them to return "Missing
Data" otherwise returns the contents of the cell. I'd rather not have
ten cells with the next set of criteria to test so any ideas would be
most welcome!

Best Regards,

CalumMurdo Kennedy

Test Cell For Multiple Criteria
 

=IF(OR(ISBLANK(N2),SUMPRODUCT(--ISNUMBER(FIND({"!","""","£","$","%","^","&",
"*",".","(",")","_","+","-","1","2","3","4","5","6","7","8","9","0"},N2) ))0
),"Missing Data",N2)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

wrote in message
oups.com...
Hi,

I have this rather poor formula to test whether whether N2 contains
certain text (£$450) or is blank.

=IF(ISBLANK(N2),"Missing
Data",IF(ISERROR(FIND("£",N2)),IF(ISERROR(FIND("$" ,N2)),IF(ISERROR(FIND("4",
N2)),IF(ISERROR(FIND("5",N2)),IF(ISERROR(FIND("0", N2)),N2,"Missing
Data"),"Missing Data"),"Missing Data"),"Missing Data"),"Missing Data"))

I would like to test for these !"£$%^&*.()_+-1234567890 (and to test
for a blank) and if the cell contains any of them to return "Missing
Data" otherwise returns the contents of the cell. I'd rather not have
ten cells with the next set of criteria to test so any ideas would be
most welcome!

Best Regards,

CalumMurdo Kennedy

Test Cell For Multiple Criteria
 

Bob Phillips wrote:
=IF(OR(ISBLANK(N2),SUMPRODUCT(--ISNUMBER(FIND({"!","""","£","$","%","^","&",
"*",".","(",")","_","+","-","1","2","3","4","5","6","7","8","9","0"},N2) ))0
),"Missing Data",N2)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)



Thank you Bob! I won't pretend to understand it tho.

Best Regards,

CalumMurdo Kennedy