Test Cell For Multiple Criteria

[calummurdo]

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

 

[Bob Phillips]

=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)

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

 

[calummurdo]

=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