Conditional formatting with number plus text (Excel 2003)

Topic

New Reply

I want to shade a column of numbers with green if positive, red if negative and yellow if = 0.

The problem is the numbers have the word “point” after them. So it’s 0 points, 5 points, -1 point. The text prevents me from using the conditional formatting. How can I work around this? Thanks

Reply | Quote

Replies

As long as there is a space between the number and “point” you can use the following as your formulas:
For items >0
=LEFT(A1,FIND(” “,A1))*1>0

For items =0
=LEFT(A1,FIND(” “,A1))*1=0

For items >0
=LEFT(A1,FIND(” “,A1))*1<0

Reply | Quote

thanks, worked great!

Reply | Quote