Using rules to color going wrong

[lu1zuk]

Sobe e Desce 2.0.xlsx
	F
2	Tempo
3	7
4	7
5	7
6	7
7	7
8	7
9	7
10	7
11	6
12	6
13	6
14	6
15	6
16	5
17	5
18	5
19	5
20	5
21	5
22	4
23	4
24	4
25	4
26	4
27	4
28	4
Principal

Cell Formulas
Range	Formula
F3:F28	F3	=SUBSTITUTE(E3,"H","")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F:F	Cell Value	<24	text	NO
F:F	Cell Value	>30	text	NO
F:F	Cell Value	between 25 and 29	text	NO

Trying to use rules to color this part, but if im using this formula "substitute" it show that they are cell>30 and paint in green, if its a value without the substitute its works fine, how to correct this?
The substitute things is because i have this

Sobe e Desce 2.0.xlsx
	A	B		D		F
3	??́ moranguinho761651070375690261	7 Horas 32 Minutos 3 Segundos		892270088286273586, 901201926312566845, 923766741597835284, 869353363677020214, 941733727610998804, 945790336070983750, 869353363815411758, 869353363815411754, 869353363635073068, 869353363647651884, 869353363807010852, 869353363635073070, 869353363677020217, 879515744549863484, 970519691330216006, 869353363794444395, 869353363677020210, 879479406039531562, 869353363668598801, 869353363668598798, 869353363677020212, 869353363609899038		7
4	calango749781054424088627	7 Horas 28 Minutos 37 Segundos		892270088286273586, 869353363714744338, 869353363677020215, 869353363609899042, 869353363635073066, 869353363861536791, 869353363807010852, 869353363832180801, 869353363815411753, 869353363677020210, 869353363677020212, 869353363609899038		7
5	Miojinha859619351405920307	7 Horas 26 Minutos 16 Segundos		892270088286273586, 970862133929730138, 869353363886710784, 940077814516953088, 869353363861536797, 869353363677020214, 869353363815411754, 869353363807010852, 869353363886710786, 869353363886710789, 869353363832180800, 973716553935769620, 869353363609899038		7
Principal

Cell Formulas
Range	Formula
F3:F5	F3	=SUBSTITUTE(E3,"H","")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F:F	Cell Value	<=24	text	NO
F:F	Cell Value	>=30	text	NO
F:F	Cell Value	between 25 and 29	text	NO

And on this, when its get the 3 first contents sometimes it gets the H, and i don't know how to get rid of the H without the substitute formule

 

[Joe4]

The result of your formula will be text.
If you coerce it to a number, it should work.

Try this:

=SUBSTITUTE(E3,"H","")+0

 

[Jon Peltier]

What's in column E?

It looks like your column F is not numeric but numbers stored as text. Your conditional formatting rules are based on numbers.

Change your formula in cell F3 to =--SUBSTITUTE(E3,"H","") and the result will be a number. Then your conditional formatting should work.

 

[lu1zuk]

The result of your formula will be text.
If you coerce it to a number, it should work.

Try this:

=SUBSTITUTE(E3,"H","")+0

Thank you, it worked fine, and i understand why!

What's in column E?

It looks like your column F is not numeric but numbers stored as text. Your conditional formatting rules are based on numbers.

Change your formula in cell F3 to =--SUBSTITUTE(E3,"H","") and the result will be a number. Then your conditional formatting should work.

Thank you too! it worked too, but i don't understand what " -- " do, i will search what its function on web

 

[Joe4]

Thank you too! it worked too, but i don't understand what " -- " do, i will search what its function on web

It is just a different way of coercing it to a number.
There are various ways of doing it. You could add zero like I did, multiply by 1, or do -- (which is just a double-negative sign).

 

[lu1zuk]

It is just a different way of coercing it to a number.
There are various ways of doing it. You could add zero like I did, multiply by 1, or do -- (which is just a double-negative sign).

Thank you for the explanation!

 

[Joe4]

You are welcome.
Glad we were able to help!

 

[Jon Peltier]

It is just a different way of coercing it to a number.
There are various ways of doing it. You could add zero like I did, multiply by 1, or do -- (which is just a double-negative sign).

I used to use +0 because it made sense, until someone told me that the double negative was somehow faster or computationally more efficient.