condition check for multiple words

[mwvirk]

hi

1- if a cell contains a specific text and if I want to change the cell format then it can be done for multiple words.
like in the attached picture, I want to see if the cell contains the word APL and then change the cell color. but is it possible for me can search multiple words using the single condition check?
I want to change the cell color if it contains 3 different words: APL ABC XYZ

2- if cell in range A contains only APL ABC XYZ then cell in range B show 100%

 

[awoohaw]

Book1
	A	B	C
1
2
3
4
5
6
7
8
9	GRAPL ABC XYZ		TRUE
10	UAAPL XYZ		TRUE
11	CDAPL ABC XYZ		TRUE
12	DXAPL ABC		TRUE
13	YIAPL ABC		TRUE
14	GKXYZ		TRUE
15	HSABC XYZ		TRUE
16	HBAPL XYZ		TRUE
17	FKAPL ABC		TRUE
18	LQXYZ		TRUE
19	RBABC		TRUE
20	JEAPL ABC		TRUE
21	SYABC		TRUE
22	UZ		FALSE
23	ENXYZ		TRUE
24	NOAPL ABC		TRUE
25	IUABC XYZ		TRUE
26	AB		FALSE
27	LMABC XYZ		TRUE
28	JBAPL XYZ		TRUE
29	TMABC XYZ		TRUE
30	DLAPL XYZ		TRUE
31	HPXYZ		TRUE
32	MFAPL ABC		TRUE
33	ASAPL ABC		TRUE
34	LLABC XYZ		TRUE
35	PBABC XYZ		TRUE
36	SGAPL ABC XYZ		TRUE
37	WAABC XYZ		TRUE
38	GMAPL ABC		TRUE
39
mwvirk

Cell Formulas
Range	Formula
C9:C38	C9	=(ISNUMBER(FIND("APL",A9)) +ISNUMBER(FIND("ABC",A9)) +ISNUMBER(FIND("XYZ",A9)))>0

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A9:A38	Expression	=(ISNUMBER(FIND("APL",A9)) +ISNUMBER(FIND("ABC",A9)) +ISNUMBER(FIND("XYZ",A9)))>0	text	NO

If you want to check that all 3 are in each cell then use this formula as the conditional equation:

=(ISNUMBER(FIND("APL",A9))+ISNUMBER(FIND("ABC",A9))+ISNUMBER(FIND("XYZ",A9)))= 3

 

[mwvirk]

thanks awoohaw
actually the words in the range A will be as below
APL
XYZ
ABC
DEF
XYZ
GHJ
GHA
APL
GHA
ABC
APL
APL
XYZ

I ONLY WANT TO CHANGE COLOR IF ITS APL or ABC or XYZ

 

[awoohaw]

thanks awoohaw
actually the words in the range A will be as below
APL
XYZ
ABC
DEF
XYZ
GHJ
GHA
APL
GHA
ABC
APL
APL
XYZ

I ONLY WANT TO CHANGE COLOR IF ITS APL or ABC or XYZ

then the first formula for conditional formatting should work. If you're seeking EXACT matches for one of the three then use this:

=OR("APL"=A9,"ABC"=A9,"XYZ"=A9)

 

[mwvirk]

then the first formula for conditional formatting should work. If you're seeking EXACT matches for one of the three then use this:

=OR("APL"=A9,"ABC"=A9,"XYZ"=A9)

perfect.
this one is working:
=(ISNUMBER(FIND("APL",A9)) +ISNUMBER(FIND("ABC",A9)) +ISNUMBER(FIND("XYZ",A9)))>0

just to understand, although the range A9:A38 is selected but in formula, it's only A9
then how it's working for the entire range?

now since the required words are found. i want to add a specific number in the next cell:

APL 7.5%
XYZ 7.5%
ABC 7.5%
DEF 15%
XYZ 7.5%
GHJ 15%
GHA 15%
APL 7.5%
GHA 15%
ABC 7.5%
APL 7.5%
APL 7.5%
XYZ 7.5%

only for the required words. for others it can be 15%
it's better if we can search and add 7.5% only and for all the rest it will be 15% (using else function)
then i will use another condition formatting if any thing in B is more than 15% then the cell format will be changed in a different color

 

[awoohaw]

When I create a conditional formatting rule, I find it best to apply to the top-left cell and then copy and paste using the "copy formatting tool".
What I've done in column C, which you do not need is to test the rules. The conditional formatting rule requirement is to create a rule that results in TRUE.
So, what i've done in C9 is to create a rule that works for cell A9. Then copy that to C38. If that works for all the cells then the formula is good.
Then copy the rule in C9 that is based on cell A9, into the conditional formatting for A9.

1. Select in edit mode C9​

2. copy the C9 formula​

3. select cell A9​

4. conditionally format A9, using "use a formula" method.​

5. paste the formula in the formula bar.​

6. choose your formatting.​

7. Click OK all the way out.​

8 Select A9​

9 Click the format painter​

10 drag down.​

Are you saying you have multiple conditions in the cells for conditional formatting?
That is not particularly kind to the forum.

Based on what you've said, I am guessing you need to add additional CF conditions.
Before I go any further,.. are there other surprises about your question.
As I read it now you have 4 scenarios:

1. the 3 text strings are not present and the percent in B is < 15% NO CF​

2. the 3 text strings are present and the percent in B is < 15% Rule 1 of CF​

3. the 3 text strings are not present and the percent in B is >= 15% Rule 2 of CF​

4. the 3 text strings are present and the percent in B is >= 15% Rule 3 of CF​

is that correct, is there anything else?

 

[mwvirk]

if the cell value A1:A25 contains any of these (APL or ABC or XYZ), then cell B1:B25 should automatically input a number 7.5%
In any other words, it should input 15%
e.g. if my word in A1 is APL then right now i am typing 7.5% it manually in B1

 

[awoohaw]

this should work:

=IF((ISNUMBER(FIND("APL",A9)) +ISNUMBER(FIND("ABC",A9)) +ISNUMBER(FIND("XYZ",A9)))>0,.075,.15)

(it is using the boolean formula that was in Cell C9 and putting it into a IF function.)

Book1
	A	B
1
2
3
4
5
6
7
8	GRAPL ABC XYZ	7.5%
9	UAAPL XYZ	7.5%
10	CDAPL ABC XYZ	7.5%
11	DXAPL ABC	7.5%
12	YIAPL ABC	7.5%
13	GKXYZ	7.5%
14	HSABC XYZ	7.5%
15	HBAPL XYZ	7.5%
16	FKAPL ABC	7.5%
17	LQXYZ	7.5%
18	RBABC	7.5%
19	JEAPL ABC	7.5%
20	SYABC	7.5%
21	UZ	15.0%
22	ENXYZ	7.5%
23	NOAPL ABC	7.5%
24	IUABC XYZ	7.5%
25	AB	15.0%
26	LMABC XYZ	7.5%
27	JBAPL XYZ	7.5%
28	TMABC XYZ	7.5%
29	DLAPL XYZ	7.5%
30	HPXYZ	7.5%
31	MFAPL ABC	7.5%
32	ASAPL ABC	7.5%
33	LLABC XYZ	7.5%
34	PBABC XYZ	7.5%
35	SGAPL ABC XYZ	7.5%
36	WAABC XYZ	7.5%
37	GMAPL ABC	7.5%
mwvirk

Cell Formulas
Range	Formula
B8:B37	B8	=IF((ISNUMBER(FIND("APL",A8)) +ISNUMBER(FIND("ABC",A8)) +ISNUMBER(FIND("XYZ",A8)))>0,0.075,0.15)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A8:A37	Expression	=(ISNUMBER(FIND("APL",A8)) +ISNUMBER(FIND("ABC",A8)) +ISNUMBER(FIND("XYZ",A8)))>0	text	NO

 

[mwvirk]

thank you awoohaw
7.5% and 15% is showing correctly.
my cell range for the actual sheet is 2:1200
so when i copied the formula in this range, i am getting 15% even if there is no word in cell A
how can i remove all these 0s
i will share the screenshot of XL2BB of my actual sheet.
thank a lot for the help. appreciate.

MWVirk PSX Portfolio v0001 (Testing Payouts Sheet) (Testing Copy Only).xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	Ex-Date	Script	Shares	Face Value	Cash Dividend %	Cash Dividend Receivable	Cash Dividend Receivable Per Share	Tax Deductions %	Tax Deductions Amount	Cash Dividend Tax Charged Per Share	Cash Dividend Received Per Share	Cash Dividend Received	Dividend Warrant Received	Date Dividend Cash Received	Bonus Shares %	Bonus Shares	Bonus Shares Confirmation Received	Date Bonus Shares Received	Right Shares %	Right Shares	Right Shares Premium	Right Shares Face Value With Premium	Right Shares Confirmation Received	Date Right Shares Received
2	15-Apr-2021	PKGS	100	10	225.00%	2,250.00	22.50	15.00%	338.00	3.38	19.12	1,912.00	Yes	25-May-2021	0.00%	0
3	16-Sep-2021	KOHE	1,000	10	52.50%	5,250.00	5.25	7.50%	394.00	0.39	4.86	4,856.00	Yes	04-Oct-2021	0.00%	0
4	06-Oct-2021	NATF	1,000	5	100.00%	5,000.00	5.00	15.00%	750.00	0.75	4.25	4,250.00	Yes	03-Nov-2021	25.00%	250	Yes	05-Nov-2021
5	10-Nov-2021	SPWL	5,000	10	20.00%	10,000.00	2.00	7.50%	750.00	0.15	1.85	9,250.00	Yes	25-Nov-2021	0.00%	0
6	10-Nov-2021	GHGL	2,000	10	15.00%	3,000.00	1.50	15.00%	450.00	0.23	1.28	2,550.00	Yes	24-Nov-2021	0.00%	0
7	10-Dec-2021	KOHE	1,000	10	77.50%	7,750.00	7.75	7.50%	581.25	0.58	7.17	7,168.75	Yes	23-Dec-2021	0.00%	0
8	31-Jan-2022	SPWL	5,000	10	50.00%	25,000.00	5.00	7.50%	1,875.00	0.38	4.63	23,125.00	Yes	21-Feb-2022	0.00%	0
9	07-Feb-2022	KAPCO	3,500	10	40.00%	14,000.00	4.00	15.00%	2,100.00	0.60	3.40	11,900.00	Yes	24-Feb-2022	0.00%	0
10	10-Feb-2022	APL	500	10	150.00%	7,500.00	15.00	15.00%	1,125.00	2.25	12.75	6,375.00	Yes	01-Mar-2022	0.00%	0
11	24-Feb-2022	KOHE	1,000	10	27.50%	2,750.00	2.75	7.50%	206.25	0.21	2.54	2,543.75	Yes	08-Mar-2022	0.00%	0
12	28-Feb-2022	SUTM	200	10	50.00%	1,000.00	5.00	15.00%	150.00	0.75	4.25	850.00	Yes	15-Mar-2022	0.00%	0
13	18-Apr-2022	PAKOXY	520	10	0.00%	0.00	0.00	15.00%	0.00	0.00	0.00	0.00			25.00%	130	Yes	19-May-2022
14	13-Apr-2022	SPWL	5,000	10	10.00%	5,000.00	1.00	7.50%	375.00	0.08	0.93	4,625.00	Yes	18-May-2022	0.00%	0
15	20-Apr-2022	SGF	1,000	10	20.00%	2,000.00	2.00	15.00%	300.00	0.30	1.70	1,700.00	Yes	18-May-2022	0.00%	0
16	27-Apr-2022	ENGRO	500	10	120.00%	6,000.00	12.00	15.00%	900.00	1.80	10.20	5,100.00	Yes	17-May-2022	0.00%	0
17	13-May-2022	PABC	1,000	10	15.00%	1,500.00	1.50	15.00%	225.00	0.23	1.28	1,275.00	Yes	26-May-2022	0.00%	0
18	21-Jul-2022	SPWL	5,000	10	45.00%	22,500.00	4.50	7.50%	1,687.00	0.34	4.16	20,813.00	Yes	05-Aug-2022	0.00%	0
19	11-Aug-2022	KOHE	1,000	10	15.00%	1,500.00	1.50	7.50%	113.00	0.11	1.39	1,387.00	Yes	19-Aug-2022	0.00%	0
20	18-Aug-2022	WAVES	7,500	10	0.00%	0.00	0.00	15.00%	0.00	0.00	0.00	0.00			20.00%	1,500	Yes	05-Dec-2022
21	20-Sep-2022	CEPB	2,150	10	0.00%	0.00	0.00	15.00%	0.00	0.00	0.00	0.00			10.00%	215	Yes	21-Oct-2022
22	11-Oct-2022	HUBC	30	10	155.00%	465.00	15.50	7.50%	34.88	1.16	14.34	430.12	Yes	01-Nov-2022	0.00%	0
23	12-Oct-2022	NATF	8,250	5	100.00%	41,250.00	5.00	15.00%	6,187.50	0.75	4.25	35,062.50	Yes	01-Nov-2022	0.00%	0
24	28-Oct-2022	ENGRO	500	10	100.00%	5,000.00	10.00	15.00%	750.00	1.50	8.50	4,250.00	Yes	16-Nov-2022	0.00%	0
25	04-Nov-2022	SPWL	5,000	10	15.00%	7,500.00	1.50	7.50%	563.00	0.11	1.39	6,937.00	Yes	21-Nov-2022	0.00%	0
26	09-Nov-2022	FABL	1,000	10	55.00%	5,500.00	5.50	15.00%	825.00	0.83	4.68	4,675.00	Yes	23-Nov-2022	0.00%	0
27	02-Dec-2022	GGL	11,000	10	0.00%	0.00	0.00	15.00%	0.00	0.00	0.00	0.00			10.00%	1,100	Yes	16-Dec-2022
28	23-Feb-2023	KOHE	1,000	10	30.00%	3,000.00	3.00	7.50%	225.00	0.23	2.78	2,775.00	Yes	06-Mar-2023	0.00%	0
29	07-Mar-2023	KAPCO	3,500	10	35.00%	12,250.00	3.50	15.00%	1,838.00	0.53	2.97	10,412.00	Yes	21-Mar-2023	0.00%	0
30	20-Apr-2023	PAKOXY	1,000	10	0.00%	0.00	0.00	15.00%	0.00	0.00	0.00	0.00			25.00%	250	Yes	20-May-2023
31	20-Apr-2023	SPWL	5,000	10	25.00%	12,500.00	2.50	7.50%	938.00	0.19	2.31	11,562.00	Yes	05-May-2023	0.00%	0
32	03-May-2023	KOHE	1,000	10	20.00%	2,000.00	2.00	7.50%	150.00	0.15	1.85	1,850.00	Yes	11-May-2023	0.00%	0
33	28-Sep-2023	APL	2,000	10	150.00%	30,000.00	15.00	15.00%	4,500.00	2.25	12.75	25,500.00	No		0.00%	0
34	28-Sep-2023	POL	2,000	10	600.00%	120,000.00	60.00	15.00%	18,000.00	9.00	51.00	102,000.00	No		0.00%	0
35	06-Oct-2023	HUBC	500	10	60.00%	3,000.00	6.00	7.50%	225.00	0.45	5.55	2,775.00	No		0.00%	0
36	18-Oct-2023	AIRLINK	3,000	10	25.00%	7,500.00	2.50	15.00%	1,125.00	0.38	2.13	6,375.00	No		0.00%	0
37	19-Oct-2023	TGL	5,000	10	60.00%	30,000.00	6.00	15.00%	4,500.00	0.90	5.10	25,500.00	No		0.00%	0
38					0.00%	0.00	0.00	15.00%	0.00	0.00	0.00	0.00			0.00%	0
PSX Payouts Status

Cell Formulas
Range	Formula
F2:F38	F2	=(C2*E2)*D2
G2:G38	G2	=IF(C2>0,F2/C2,0)
H2:H38	H2	=IF((ISNUMBER(FIND("SPWL",B2)) +ISNUMBER(FIND("KOHE",B2)) +ISNUMBER(FIND("HUBC",B2)))>0,0.075,0.15)
J2:J38	J2	=G2-K2
K2:K38	K2	=IF(C2>0,L2/C2,0)
L23:L38,L2:L21	L2	=F2-I2
I32:I38,I30,I26:I28,I20:I24,I4:I17	I4	=F4*H4
P2:P38	P2	=C2*O2
T2:T38	T2	=IF(AND(C2<>"",S2<>""),C2*S2,"")
V2:V38	V2	=IF(AND(D2<>"",U2<>""),D2+U2,"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B40	Expression	=OR("SPWL"=B2,"KOHE"=B2,"HUBC"=B2)	text	NO
M1001:M1131,Q1001:Q1131,M2:M168,Q2:Q168,W2:W168	Cell Value	contains "No"	text	NO
M1001:M1131,Q1001:Q1131,M2:M168,Q2:Q168,W2:W168	Cell Value	contains "Yes"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
B2:B38	List	=#REF!

 

[mwvirk]

please note that APL ABC XYZ were temporary words. since i am sharing the actual sheet, you can find the correct cells and words.
thanks