Slicing text conditional formatting

[Kenyon Graham]

Hi,

I am a little foxed.

I have a cell entry 08/10

Two numbers separated by a forward slash as text.

The idea is that is a number is less than 10 its filled red if not green if there is an entry

I was hoping that the following screen shot would do the trick, but alas somethings not quite right.

Could I have some guidance on this preferably in excel.......

 

[Joe4]

Welcome to the Board!

If the idea is to just check if the first number is less than the second, here is a Conditional Formatting formula you could use to check that (example on cell O1):

=LEFT(O1,SEARCH("/",O1)-1) < MID(O1,SEARCH("/",O1)+1,LEN(O1))

 

[Kenyon Graham]

Thanks Joe4,

Your formula does work, however i was looking to have a fixed value in this case 10 ie <10 turns red >10 turns green.

=LEFT(AT58,SEARCH("/",AT58)-1) < MID(AT58,SEARCH("/",AT58)+1,LEN(AT58))

by slicing you are picking up only 1 digit(character), the entry will have typically 12/14, 08/10 etc the 0 is added to give 5 charaters for future processing.

=LEFT(AT58,SEARCH("/",AT58)-1) < 10 does not seem to work.

any thoughts

Kenyon

 

[Joe4]

By default, LEFT functions return strings, not numbers. You need to do some sort of mathematical function on them to convert to numbers.
So try:

=(LEFT(AT58,SEARCH("/",AT58)-1)*1) < 10