# Excel Conditional Formatting cell's value that has number suffix, like 99th, 23rd, or 1st

So I am filling a cell with Blue, Green, Yellow, or Red based on a Percentile Rank that is returned, that is numbers like `( >=90th=Blue, >=60th=Green, >=30th=Yellow, <30th=Red )`

Currently I am using this Conditional Formatting in each of those cells, Which Does Work!

`=VALUE(LEFT(INDIRECT(ADDRESS(ROW(),COLUMN())),LEN((INDIRECT(ADDRESS(ROW(),COLUMN()))))-2)) <30`

Basically what this does is find the row and address of cell containing the formula, which works great with Conditional Formatting by true formula. In my case `INDIRECT(ADDRESS(ROW(),COLUMN())) = \$E\$34` as an example.

So after some evaluating it looks like this `=VALUE(LEFT(\$E\$34,LEN((\$E\$34-2)) <30`

If `\$E\$34 = 4th` Then my formula evaluates to True since its less than 30 and colors the cell Red for me.

### So my question is, is there an easier or more efficient way to format the cell based on these types of values that have suffixes like, st, nd, rd, and th for numbers like 4th, 13th, and 100th?

Thanks,

-
There shouldn't be any need to use the whole INDIRECT/ADDRESS construction - you should be able to refer to the top left cell of your range in the formula so it only has to look like your shorter version, e.g. if range to format is E34:E100 select than range and apply this formula `=LEFT(E34,LEN(E34)-2)+0<30` - that will work for the whole range – barry houdini May 30 '13 at 19:45
Thanks Barry, That also does work. I also like how just adding 0 to the formula makes the text a number. I had tried to reference the actual cell range relative like you have but was running into problems in the formula bar of the conditional fomatting. Thanks for your comment. – Scheballs May 31 '13 at 11:24