Forum Discussion
Conditional Formatting degrees
- You could use text to number tool each time or use a helper column that will strip the degree symbol and convert to number or use custom formula in conditional formatting like:
=(--LEFT(K2,LEN(K2-1))<90
or maybe
=(--SUBSTITUTE(K2,"°",""))<90
Is the ° symbol part of the value? If so, the values are text, not numbers, and you should convert them to numbers:
Select the values.
Press Ctrl+H to activate the Replace dialog.
Enter ° in the 'Find what' box and leave the 'Replace with' box empty.
Click 'Replace All', then close the Replace dialog.
Apply the custom number format 00.00° to the selected cells.
The conditional formatting rule(s) should now work as intended.
- You could use text to number tool each time or use a helper column that will strip the degree symbol and convert to number or use custom formula in conditional formatting like:
=(--LEFT(K2,LEN(K2-1))<90
or maybe
=(--SUBSTITUTE(K2,"°",""))<90- Yes that's it! now I format based on true/false. Thank you so much.
actually in Conditional formatting - new rule - there is a "Use a formula to determine which cells to format" and you can just enter the formula there
just make sure the formula is based on the upper left cell of the Applied To range so the above formula is based on highlighting and applying to a range starting in K2 and going down but if you highlight the whole column (K:K) then the first cell is K1 and you should use K1 in that formula instead of K2. (it is treated as if you entered the formula in that cell and copied/filled down/right accordingly so if you start in K1 but enter K2 then every cell will get highlighted based on the value of the cell below it.)