How to Conditional Format using Right(), Left(), or Mid() with text values?

[NamssoB]

I have column H setup with software version numbers, formatted as follows (starting at cell H2):

H2: 1.0.0000
H3: 2.0.0000
H4: 3.0.1037
H5: 3.0.1062
H6: 3.0.1098

I'd like to highlight any cell in green that has the most recent version (1098), Yellow if it's at least a version 3.x.x or higher, and then Red if anything else.

1.0.0000
2.0.0000
3.0.1037
3.0.1062
3.0.1098

What are the formula(s) I should put into the conditional formatting dialogue to accomplish this? if I make the default color RED, then I'm guessing I only need two conditions (one for Yellow and another for Green)?

BONUS: If I can have one cell at the top (H1, for example) that contains the "current" version so I don't have to hard code the version into the conditional formatting formula.

The problems I've had thus far (nothing seems to work for me!) is that 1) all 0s get converted down to one 0 instead of the four digits, and 2) these are text values, obviously not numerical so using <> symbols don't seem to work either.

 

[6StringJazzer]

Easiest to format entire column H since this will not use array formulas.

As you said, format the entire column Red as a default. You don't need a rule for it.

Green

=RIGHT(H1,4)="1098"

For the bonus points, using H1 as the current version

=RIGHT(H1,4)=H$1

Yellow

=VALUE(LEFT(H1,FIND(".",H1)-1))>=3

If it's all 0s you don't care unless the latest version is 0000, unlikely.

 

[NamssoB]

Excellent - thank you!

 

[6StringJazzer]

Oh, if you put a number in H1 as the current version, rather than text, you may need to do this. Not sure, haven't tested.

=VALUE(RIGHT(H1,4))=H$1