excel halp!

TdC

Trem's hunky sex love muffin
Joined
Dec 20, 2003
Messages
30,801
Dudes,

I need a formula that returns a given set of 1's and 0's when a certain letter or combination is present in a cell. I've tried this:

Code:
=IF(FIND("CRU";A2);1&CHAR(44)&1&CHAR(44)&0&CHAR(44)&1&CHAR(44)&0&CHAR(44)&1;IF(FIND("C";A2);1&CHAR(44)&1&CHAR(44)&1&CHAR(44)&0&CHAR(44)&0&CHAR(44)&1;IF(FIND("R";A2);1&CHAR(44)&1&CHAR(44)&0&CHAR(44)&0&CHAR(44)&0&CHAR(44)&1;IF(FIND("U";A2);1&CHAR(44)&1&CHAR(44)&0&CHAR(44)&1&CHAR(44)&0&CHAR(44)&1;IF(FIND("CR";A2);1&CHAR(44)&1&CHAR(44)&1&CHAR(44)&0&CHAR(44)&0&CHAR(44)&1;IF(FIND("CU";A2);1&CHAR(44)&1&CHAR(44)&1&CHAR(44)&1&CHAR(44)&0&CHAR(44)&1;IF(FIND("RU";A2);1&CHAR(44)&1&CHAR(44)&0&CHAR(44)&1&CHAR(44)&0&CHAR(44)&1;0)))))))

but the barstand will only match the first IF(FIND()) :eek: when another combination is present it fails :eek:

example:

Code:
C #VALUE!
R #VALUE!
U #VALUE!
CR #VALUE!
CU #VALUE!
RU #VALUE!
CRU 1,1,0,1,0,1

I'm prolly forgetting something stupid. Help meh pls guys :D
 

TdC

Trem's hunky sex love muffin
Joined
Dec 20, 2003
Messages
30,801
with Throd's help I have sussed it :D

Code:
=IF(ISERROR(FIND("CRU";A2));D2;IF(ISERROR(FIND("C";A2));D2;IF(ISERROR(FIND("R";A2));D2;IF(ISERROR(FIND("U";A2));D2;IF(ISERROR(FIND("CR";A2));D2;IF(ISERROR(FIND("CU";A2));D2;IF(ISERROR(FIND("RU";A2));D2;0)))))))

(D2 and it's brethren contain the code I need it to look up :)
 

TdC

Trem's hunky sex love muffin
Joined
Dec 20, 2003
Messages
30,801
and now I've really sussed it :D

Code:
=IF(ISERROR(SEARCH("C";B4));IF(ISERROR(SEARCH("R";B4));IF(ISERROR(SEARCH("U";B4));IF(ISERROR(SEARCH("CR";B4));IF(ISERROR(SEARCH("CU";B4));IF(ISERROR(SEARCH("RU";B4));IF(ISERROR(SEARCH("CRU";B4));0;Sheet1!M$12);Sheet1!N$12);Sheet1!O$12);Sheet1!P$12);Sheet1!Q$12);Sheet1!R$12);Sheet1!S$12)
 

Users who are viewing this thread

Top Bottom