Good morning everyone
I work for an animal-related charity. We have a long, complicated roster developed in Excel that can be difficult to manage.
Volunteer shifts are divided by days (7 columns) and extend to over 100 rows. I need to be able to order the rows by start times, which are given in 24-hour format. However, the cells each contain a big, undifferentiated block of data indicating the volunteer's individual number (eg SP123), their respective start time (eg 14:30) and their job location and type for that day (eg 8219).
How do I create a formula that will 'see' the start time in each cell and allow me to order those start times within individual columns indicating days? I want to be able to instantly see the earliest Monday shift, followed in the next row by the second-earliest, then the third-earliest, and so on. The last row should show the latest shift of that particular day.
Please find attached a sample of the roster.
Many thanks, Kent
I work for an animal-related charity. We have a long, complicated roster developed in Excel that can be difficult to manage.
Volunteer shifts are divided by days (7 columns) and extend to over 100 rows. I need to be able to order the rows by start times, which are given in 24-hour format. However, the cells each contain a big, undifferentiated block of data indicating the volunteer's individual number (eg SP123), their respective start time (eg 14:30) and their job location and type for that day (eg 8219).
How do I create a formula that will 'see' the start time in each cell and allow me to order those start times within individual columns indicating days? I want to be able to instantly see the earliest Monday shift, followed in the next row by the second-earliest, then the third-earliest, and so on. The last row should show the latest shift of that particular day.
Please find attached a sample of the roster.
Many thanks, Kent
| Sample Book.xlsx | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | |||
| 1 | SUN | MON | TUE | WED | THU | FRI | SAT | |||
| 2 | 1 | OR | SP705 14:13 Pilot | SP323 14:19 9029 | SP043 14:35 8237 | SP118 14:25 8077 | SP416 14:51 8419 | OR | ||
| 3 | 00:00 | 08:40 | 08:11 | 08:00 | 08:23 | 08:00 | 00:00 | |||
| 4 | 2 | OR | SP213 03:22 8904 | SP517 03:11 8272 | SP518 03:35 SG 8213 | OR | SP018 03:10 SG 8207 | SP545 03:10 0177 | ||
| 5 | 00:00 | 08:00 | 08:00 | 08:00 | 00:00 | 08:00 | 08:00 | |||
| 6 | 3 | SP411 13:24 8417 | SP240 13:45 8759 | SP239 13:31 8757 | SP039 13:31 8127 | SP237 13:25 SG 8755 | OR | OR | ||
| 7 | 08:00 | 08:08 | 08:00 | 08:27 | 08:00 | 00:00 | 00:00 | |||
| 8 | 4 | OR | OR | SP614 07:00 9934 | SP524 06:54 0177 | SP226 07:11 8717 | SP228 07:45 8721 | SP548 08:25 0177 | ||
| 9 | 00:00 | 00:00 | 08:00 | 08:00 | 08:00 | 08:00 | 08:00 | |||
| 10 | 5 | SP206 10:29 8739 | SP615 10:00 9934 | OR | SP232 11:29 8745 | SP533 12:00 SG 0177 | SP036 11:55 8123 | OR | ||
| 11 | 08:23 | 08:00 | 00:00 | 08:00 | 08:00 | 08:00 | 00:00 | |||
| 12 | 6 | OR | SP112 03:25 8006 | SP021 03:48 8201 | OR | SP516 00:05 0177 | SP610 03:00 9934 | SP544 00:05 0177 | ||
| 13 | 00:00 | 08:00 | 08:00 | 00:00 | 08:00 | 08:00 | 08:00 | |||
| 14 | 7 | SP109 15:10 8089 | SP326 16:14 8331 | OR | OR | SP246 14:22 8761 | SP248 14:50 8797 | SP430 15:42 8421 | ||
| 15 | 08:04 | 08:00 | 00:00 | 00:00 | 08:00 | 08:00 | 08:00 | |||
| 16 | 8 | OR | SP013 01:54 7204 | SP014 01:55 7216 | SP016 01:56 7210 | SP608 02:00 9934 | SP212 02:56 8701 | OR | ||
| 17 | 00:00 | 08:00 | 08:11 | 08:00 | 08:00 | 06:00 | 00:00 | |||
| 18 | 9 | OR | SP532 11:47 0177 | SP116 12:04 8023 | SP233 12:18 8935 | OR | SP234 12:21 8749 | SP262 10:45 8739 | ||
| 19 | 00:00 | 08:00 | 08:00 | 08:03 | 00:00 | 08:00 | 08:00 | |||
| 20 | 10 | SP203 05:58 8717 | SP614 07:00 9934 | SP524 06:54 0177 | SP028 06:50 8205 | SP523 06:42 0177 | OR | OR | ||
| 21 | 08:00 | 08:00 | 08:00 | 08:00 | 08:00 | 00:00 | 00:00 | |||
Sheet1 | ||||||||||
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| E14 | Expression | =SEARCH("*Annual*",H123) | text | NO |
| E14 | Expression | =SEARCH("*REST*",H123) | text | NO |
| E14 | Expression | =SEARCH("* SP*",H123) | text | NO |
| E14 | Expression | =SEARCH("*OR*",H123) | text | NO |
| D10 | Expression | =SEARCH("*Annual*",B113) | text | NO |
| D10 | Expression | =SEARCH("*REST*",B113) | text | NO |
| D10 | Expression | =SEARCH("* SP*",B113) | text | NO |
| D10 | Expression | =SEARCH("*OR*",B113) | text | NO |
| B4 | Expression | =SEARCH("*Annual*",F247) | text | NO |
| B4 | Expression | =SEARCH("*REST*",F247) | text | NO |
| B4 | Expression | =SEARCH("* SP*",F247) | text | NO |
| B4 | Expression | =SEARCH("*OR*",F247) | text | NO |
| D16 | Expression | =SEARCH("*Annual*",#REF!) | text | NO |
| D16 | Expression | =SEARCH("*REST*",#REF!) | text | NO |
| D16 | Expression | =SEARCH("* SP*",#REF!) | text | NO |
| D16 | Expression | =SEARCH("*OR*",#REF!) | text | NO |
| H18 | Expression | =SEARCH("*Annual*",#REF!) | text | NO |
| H18 | Expression | =SEARCH("*REST*",#REF!) | text | NO |
| H18 | Expression | =SEARCH("* SP*",#REF!) | text | NO |
| H18 | Expression | =SEARCH("*OR*",#REF!) | text | NO |
| E2 | Expression | =SEARCH("*Annual*",#REF!) | text | NO |
| E2 | Expression | =SEARCH("*REST*",#REF!) | text | NO |
| E2 | Expression | =SEARCH("* SP*",#REF!) | text | NO |
| E2 | Expression | =SEARCH("*OR*",#REF!) | text | NO |
| F4 | Expression | =SEARCH("*Annual*",B211) | text | NO |
| F4 | Expression | =SEARCH("*REST*",B211) | text | NO |
| F4 | Expression | =SEARCH("* SP*",B211) | text | NO |
| F4 | Expression | =SEARCH("*OR*",B211) | text | NO |
| D2 | Expression | =SEARCH("*Annual*",#REF!) | text | NO |
| D2 | Expression | =SEARCH("*REST*",#REF!) | text | NO |
| D2 | Expression | =SEARCH("* SP*",#REF!) | text | NO |
| D2 | Expression | =SEARCH("*OR*",#REF!) | text | NO |
| B20 | Expression | =SEARCH("*Annual*",#REF!) | text | NO |
| B20 | Expression | =SEARCH("*REST*",#REF!) | text | NO |
| B20 | Expression | =SEARCH("* SP*",#REF!) | text | NO |
| B20 | Expression | =SEARCH("*OR*",#REF!) | text | NO |
| G8 | Expression | =SEARCH("*Annual*",#REF!) | text | NO |
| G8 | Expression | =SEARCH("*REST*",#REF!) | text | NO |
| G8 | Expression | =SEARCH("* SP*",#REF!) | text | NO |
| G8 | Expression | =SEARCH("*OR*",#REF!) | text | NO |
| E18 | Expression | =SEARCH("*Annual*",E225) | text | NO |
| E18 | Expression | =SEARCH("*REST*",E225) | text | NO |
| E18 | Expression | =SEARCH("* SP*",E225) | text | NO |
| E18 | Expression | =SEARCH("*OR*",E225) | text | NO |
| H16 | Expression | =SEARCH("*Annual*",B11) | text | NO |
| H16 | Expression | =SEARCH("*REST*",B11) | text | NO |
| H16 | Expression | =SEARCH("* SP*",B11) | text | NO |
| H16 | Expression | =SEARCH("*OR*",B11) | text | NO |
| C10 | Expression | =SEARCH("*Annual*",XEY145) | text | NO |
| C10 | Expression | =SEARCH("*REST*",XEY145) | text | NO |
| C10 | Expression | =SEARCH("* SP*",XEY145) | text | NO |
| C10 | Expression | =SEARCH("*OR*",XEY145) | text | NO |
| C4 | Expression | =SEARCH("*Annual*",C99) | text | NO |
| C4 | Expression | =SEARCH("*REST*",C99) | text | NO |
| C4 | Expression | =SEARCH("* SP*",C99) | text | NO |
| C4 | Expression | =SEARCH("*OR*",C99) | text | NO |
| D18 | Expression | =SEARCH("*Annual*",D129) | text | NO |
| D18 | Expression | =SEARCH("*REST*",D129) | text | NO |
| D18 | Expression | =SEARCH("* SP*",D129) | text | NO |
| D18 | Expression | =SEARCH("*OR*",D129) | text | NO |
| F14 | Expression | =SEARCH("*Annual*",F129) | text | NO |
| F14 | Expression | =SEARCH("*REST*",F129) | text | NO |
| F14 | Expression | =SEARCH("* SP*",F129) | text | NO |
| F14 | Expression | =SEARCH("*OR*",F129) | text | NO |
| F6 | Expression | =SEARCH("*Annual*",F185) | text | NO |
| F6 | Expression | =SEARCH("*REST*",F185) | text | NO |
| F6 | Expression | =SEARCH("* SP*",F185) | text | NO |
| F6 | Expression | =SEARCH("*OR*",F185) | text | NO |
| G16 | Expression | =SEARCH("*Annual*",G261) | text | NO |
| G16 | Expression | =SEARCH("*REST*",G261) | text | NO |
| G16 | Expression | =SEARCH("* SP*",G261) | text | NO |
| G16 | Expression | =SEARCH("*OR*",G261) | text | NO |
| F10 | Expression | =SEARCH("*Annual*",F65) | text | NO |
| F10 | Expression | =SEARCH("*REST*",F65) | text | NO |
| F10 | Expression | =SEARCH("* SP*",F65) | text | NO |
| F10 | Expression | =SEARCH("*OR*",F65) | text | NO |
| C6 | Expression | =SEARCH("*Annual*",C293) | text | NO |
| C6 | Expression | =SEARCH("*REST*",C293) | text | NO |
| C6 | Expression | =SEARCH("* SP*",C293) | text | NO |
| C6 | Expression | =SEARCH("*OR*",C293) | text | NO |
| G6 | Expression | =SEARCH("*Annual*",G293) | text | NO |
| G6 | Expression | =SEARCH("*REST*",G293) | text | NO |
| G6 | Expression | =SEARCH("* SP*",G293) | text | NO |
| G6 | Expression | =SEARCH("*OR*",G293) | text | NO |
| F12 | Expression | =SEARCH("*Annual*",F149) | text | NO |
| F12 | Expression | =SEARCH("*REST*",F149) | text | NO |
| F12 | Expression | =SEARCH("* SP*",F149) | text | NO |
| F12 | Expression | =SEARCH("*OR*",F149) | text | NO |
| E6 | Expression | =SEARCH("*Annual*",E245) | text | NO |
| E6 | Expression | =SEARCH("*REST*",E245) | text | NO |
| E6 | Expression | =SEARCH("* SP*",E245) | text | NO |
| E6 | Expression | =SEARCH("*OR*",E245) | text | NO |
| E10 | Expression | =SEARCH("*Annual*",E285) | text | NO |
| E10 | Expression | =SEARCH("*REST*",E285) | text | NO |
| E10 | Expression | =SEARCH("* SP*",E285) | text | NO |
| E10 | Expression | =SEARCH("*OR*",E285) | text | NO |
| C14 | Expression | =SEARCH("*Annual*",C129) | text | NO |
| C14 | Expression | =SEARCH("*REST*",C129) | text | NO |
| C14 | Expression | =SEARCH("* SP*",C129) | text | NO |
| C14 | Expression | =SEARCH("*OR*",C129) | text | NO |
| H2 | Expression | =SEARCH("*Annual*",H209) | text | NO |
| H2 | Expression | =SEARCH("*REST*",H209) | text | NO |
| H2 | Expression | =SEARCH("* SP*",H209) | text | NO |
| H2 | Expression | =SEARCH("*OR*",H209) | text | NO |
| B10 | Expression | =SEARCH("*Annual*",#REF!) | text | NO |
| B10 | Expression | =SEARCH("*REST*",#REF!) | text | NO |
| B10 | Expression | =SEARCH("* SP*",#REF!) | text | NO |
| B10 | Expression | =SEARCH("*OR*",#REF!) | text | NO |
| B2 | Expression | =SEARCH("*Annual*",D269) | text | NO |
| B2 | Expression | =SEARCH("*REST*",D269) | text | NO |
| B2 | Expression | =SEARCH("* SP*",D269) | text | NO |
| B2 | Expression | =SEARCH("*OR*",D269) | text | NO |
| C16 | Expression | =SEARCH("*Annual*",C239) | text | NO |
| C16 | Expression | =SEARCH("*REST*",C239) | text | NO |
| C16 | Expression | =SEARCH("* SP*",C239) | text | NO |
| C16 | Expression | =SEARCH("*OR*",C239) | text | NO |
| H12 | Expression | =SEARCH("*Annual*",H297) | text | NO |
| H12 | Expression | =SEARCH("*REST*",H297) | text | NO |
| H12 | Expression | =SEARCH("* SP*",H297) | text | NO |
| H12 | Expression | =SEARCH("*OR*",H297) | text | NO |
| H8 | Expression | =SEARCH("*Annual*",H13) | text | NO |
| H8 | Expression | =SEARCH("*REST*",H13) | text | NO |
| H8 | Expression | =SEARCH("* SP*",H13) | text | NO |
| H8 | Expression | =SEARCH("*OR*",H13) | text | NO |
| E20 | Expression | =SEARCH("*Annual*",E187) | text | NO |
| E20 | Expression | =SEARCH("*REST*",E187) | text | NO |
| E20 | Expression | =SEARCH("* SP*",E187) | text | NO |
| E20 | Expression | =SEARCH("*OR*",E187) | text | NO |
| G12 | Expression | =SEARCH("*Annual*",G79) | text | NO |
| G12 | Expression | =SEARCH("*REST*",G79) | text | NO |
| G12 | Expression | =SEARCH("* SP*",G79) | text | NO |
| G12 | Expression | =SEARCH("*OR*",G79) | text | NO |
| C8 | Expression | =SEARCH("*Annual*",#REF!) | text | NO |
| C8 | Expression | =SEARCH("*REST*",#REF!) | text | NO |
| C8 | Expression | =SEARCH("* SP*",#REF!) | text | NO |
| C8 | Expression | =SEARCH("*OR*",#REF!) | text | NO |
| E16 | Expression | =SEARCH("*Annual*",E47) | text | NO |
| E16 | Expression | =SEARCH("*REST*",E47) | text | NO |
| E16 | Expression | =SEARCH("* SP*",E47) | text | NO |
| E16 | Expression | =SEARCH("*OR*",E47) | text | NO |
| G4 | Expression | =SEARCH("*Annual*",G123) | text | NO |
| G4 | Expression | =SEARCH("*REST*",G123) | text | NO |
| G4 | Expression | =SEARCH("* SP*",G123) | text | NO |
| G4 | Expression | =SEARCH("*OR*",G123) | text | NO |
| D6,G2 | Expression | =SEARCH("*Annual*",#REF!) | text | NO |
| D6,G2 | Expression | =SEARCH("*REST*",#REF!) | text | NO |
| D6,G2 | Expression | =SEARCH("* SP*",#REF!) | text | NO |
| D6,G2 | Expression | =SEARCH("*OR*",#REF!) | text | NO |
| D8,G18,E4 | Expression | =SEARCH("*Annual*",#REF!) | text | NO |
| D8,G18,E4 | Expression | =SEARCH("*REST*",#REF!) | text | NO |
| D8,G18,E4 | Expression | =SEARCH("* SP*",#REF!) | text | NO |
| D8,G18,E4 | Expression | =SEARCH("*OR*",#REF!) | text | NO |
| C12 | Expression | =SEARCH("*Annual*",#REF!) | text | NO |
| C12 | Expression | =SEARCH("*REST*",#REF!) | text | NO |
| C12 | Expression | =SEARCH("* SP*",#REF!) | text | NO |
| C12 | Expression | =SEARCH("*OR*",#REF!) | text | NO |
| D12 | Expression | =SEARCH("*Annual*",#REF!) | text | NO |
| D12 | Expression | =SEARCH("*REST*",#REF!) | text | NO |
| D12 | Expression | =SEARCH("* SP*",#REF!) | text | NO |
| D12 | Expression | =SEARCH("*OR*",#REF!) | text | NO |
| B6 | Expression | =SEARCH("*Annual*",#REF!) | text | NO |
| B6 | Expression | =SEARCH("*REST*",#REF!) | text | NO |
| B6 | Expression | =SEARCH("* SP*",#REF!) | text | NO |
| B6 | Expression | =SEARCH("*OR*",#REF!) | text | NO |
| C18 | Expression | =SEARCH("*Annual*",XEY251) | text | NO |
| C18 | Expression | =SEARCH("*REST*",XEY251) | text | NO |
| C18 | Expression | =SEARCH("* SP*",XEY251) | text | NO |
| C18 | Expression | =SEARCH("*OR*",XEY251) | text | NO |
| D20 | Expression | =SEARCH("*Annual*",D171) | text | NO |
| D20 | Expression | =SEARCH("*REST*",D171) | text | NO |
| D20 | Expression | =SEARCH("* SP*",D171) | text | NO |
| D20 | Expression | =SEARCH("*OR*",D171) | text | NO |
| F16 | Expression | =SEARCH("*Annual*",XFB189) | text | NO |
| F16 | Expression | =SEARCH("*REST*",XFB189) | text | NO |
| F16 | Expression | =SEARCH("* SP*",XFB189) | text | NO |
| F16 | Expression | =SEARCH("*OR*",XFB189) | text | NO |
| G14 | Expression | =SEARCH("*Annual*",XEZ185) | text | NO |
| G14 | Expression | =SEARCH("*REST*",XEZ185) | text | NO |
| G14 | Expression | =SEARCH("* SP*",XEZ185) | text | NO |
| G14 | Expression | =SEARCH("*OR*",XEZ185) | text | NO |
| D4 | Expression | =SEARCH("*Annual*",XEZ251) | text | NO |
| D4 | Expression | =SEARCH("*REST*",XEZ251) | text | NO |
| D4 | Expression | =SEARCH("* SP*",XEZ251) | text | NO |
| D4 | Expression | =SEARCH("*OR*",XEZ251) | text | NO |
| H4 | Expression | =SEARCH("*Annual*",XFA203) | text | NO |
| H4 | Expression | =SEARCH("*REST*",XFA203) | text | NO |
| H4 | Expression | =SEARCH("* SP*",XFA203) | text | NO |
| H4 | Expression | =SEARCH("*OR*",XFA203) | text | NO |
| F20,G10 | Expression | =SEARCH("*Annual*",#REF!) | text | NO |
| F20,G10 | Expression | =SEARCH("*REST*",#REF!) | text | NO |
| F20,G10 | Expression | =SEARCH("* SP*",#REF!) | text | NO |
| F20,G10 | Expression | =SEARCH("*OR*",#REF!) | text | NO |
| H13 | Expression | =SEARCH("*Annual*",H12) | text | NO |
| H13 | Expression | =SEARCH("*REST*",H12) | text | NO |
| H13 | Expression | =SEARCH("* SP*",H12) | text | NO |
| H13 | Expression | =SEARCH("*OR*",H12) | text | NO |
| E8 | Expression | =SEARCH("*Annual*",E247) | text | NO |
| E8 | Expression | =SEARCH("*REST*",E247) | text | NO |
| E8 | Expression | =SEARCH("* SP*",E247) | text | NO |
| E8 | Expression | =SEARCH("*OR*",E247) | text | NO |
| F18 | Expression | =SEARCH("*Annual*",B1048575) | text | NO |
| F18 | Expression | =SEARCH("*REST*",B1048575) | text | NO |
| F18 | Expression | =SEARCH("* SP*",B1048575) | text | NO |
| F18 | Expression | =SEARCH("*OR*",B1048575) | text | NO |
| B18 | Expression | =SEARCH("*Annual*",F1048575) | text | NO |
| B18 | Expression | =SEARCH("*REST*",F1048575) | text | NO |
| B18 | Expression | =SEARCH("* SP*",F1048575) | text | NO |
| B18 | Expression | =SEARCH("*OR*",F1048575) | text | NO |
| D14 | Expression | =SEARCH("*Annual*",D1048483) | text | NO |
| D14 | Expression | =SEARCH("*REST*",D1048483) | text | NO |
| D14 | Expression | =SEARCH("* SP*",D1048483) | text | NO |
| D14 | Expression | =SEARCH("*OR*",D1048483) | text | NO |
| B12 | Expression | =SEARCH("*Annual*",G1048573) | text | NO |
| B12 | Expression | =SEARCH("*REST*",G1048573) | text | NO |
| B12 | Expression | =SEARCH("* SP*",G1048573) | text | NO |
| B12 | Expression | =SEARCH("*OR*",G1048573) | text | NO |
| B8 | Expression | =SEARCH("*Annual*",B15) | text | NO |
| B8 | Expression | =SEARCH("*REST*",B15) | text | NO |
| B8 | Expression | =SEARCH("* SP*",B15) | text | NO |
| B8 | Expression | =SEARCH("*OR*",B15) | text | NO |
| H10 | Expression | =SEARCH("*Annual*",H1048573) | text | NO |
| H10 | Expression | =SEARCH("*REST*",H1048573) | text | NO |
| H10 | Expression | =SEARCH("* SP*",H1048573) | text | NO |
| H10 | Expression | =SEARCH("*OR*",H1048573) | text | NO |
| G20 | Expression | =SEARCH("*Annual*",G103) | text | NO |
| G20 | Expression | =SEARCH("*REST*",G103) | text | NO |
| G20 | Expression | =SEARCH("* SP*",G103) | text | NO |
| G20 | Expression | =SEARCH("*OR*",G103) | text | NO |
| H20,C2,C20 | Expression | =SEARCH("*Annual*",C29) | text | NO |
| H20,C2,C20 | Expression | =SEARCH("*REST*",C29) | text | NO |
| H20,C2,C20 | Expression | =SEARCH("* SP*",C29) | text | NO |
| H20,C2,C20 | Expression | =SEARCH("*OR*",C29) | text | NO |
| F2 | Expression | =SEARCH("*Annual*",F45) | text | NO |
| F2 | Expression | =SEARCH("*REST*",F45) | text | NO |
| F2 | Expression | =SEARCH("* SP*",F45) | text | NO |
| F2 | Expression | =SEARCH("*OR*",F45) | text | NO |
| H14 | Expression | =SEARCH("*Annual*",#REF!) | text | NO |
| H14 | Expression | =SEARCH("*REST*",#REF!) | text | NO |
| H14 | Expression | =SEARCH("* SP*",#REF!) | text | NO |
| H14 | Expression | =SEARCH("*OR*",#REF!) | text | NO |
| B14,F8 | Expression | =SEARCH("*Annual*",#REF!) | text | NO |
| B14,F8 | Expression | =SEARCH("*REST*",#REF!) | text | NO |
| B14,F8 | Expression | =SEARCH("* SP*",#REF!) | text | NO |
| B14,F8 | Expression | =SEARCH("*OR*",#REF!) | text | NO |
| B2:H21 | Cell Value | contains " SP" | text | NO |
| B2:H21 | Cell Value | contains "SG" | text | NO |
| B3:H3,B17:H17,B13:G13,B5:H5,B9:H9,B11:H11,B19:H19,B7:H7,B15:H15,B21:H21 | Expression | =SEARCH("*Annual*",B2) | text | NO |
| B2:H21 | Cell Value | contains "Annual" | text | NO |
| B2:H21 | Cell Value | contains "OR" | text | NO |
| B2:H21 | Cell Value | contains "REST" | text | NO |
| B3:H3,B17:H17,B13:G13,B5:H5,B9:H9,B11:H11,B19:H19,B7:H7,B15:H15,B21:H21 | Expression | =SEARCH("*REST*",B2) | text | NO |
| B3:H3,B17:H17,B13:G13,B5:H5,B9:H9,B11:H11,B19:H19,B7:H7,B15:H15,B21:H21 | Expression | =SEARCH("* SP*",B2) | text | NO |
| B2:H21 | Expression | =ISERROR(B2) | text | YES |
| B3:H3,B17:H17,B13:G13,B5:H5,B9:H9,B11:H11,B19:H19,B7:H7,B15:H15,B21:H21 | Expression | =SEARCH("*OR*",B2) | text | NO |
