Conditional format on text field?

[JenniferMurphy]

I have a column of text values representing the number of weeks remaining before some deadline.

[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD="align: center"]Lead Time[/TD]
[/TR]
[TR]
[TD="align: right"]25 Wks [/TD]
[/TR]
[TR]
[TD="align: right"]28 Wks [/TD]
[/TR]
[TR]
[TD="align: right"]30 Wks [/TD]
[/TR]
[TR]
[TD="align: right"]18 Wks[/TD]
[/TR]
[TR]
[TD="align: right"]35 Wks[/TD]
[/TR]
[TR]
[TD="align: right"]3 Wks[/TD]
[/TR]
[TR]
[TD="align: right"]27 Wks [/TD]
[/TR]
[TR]
[TD="align: right"]38 Wks [/TD]
[/TR]
</tbody>[/TABLE]

I would like like to highlight (fill color) the cells depending on the value. For example, set the fill to red for cells that are less than 2 wks. But these are text values, not numeric. Is there any way to do this?

It would be difficult for me to change these values to numeric.

 

[jtakw]

Hi,

Use B2 formula as your CF formula:

Book1
	A	B
1	Lead Time
2	25 Wks	FALSE
3	28 Wks	FALSE
4	30 Wks	FALSE
5	18 Wks	FALSE
6	35 Wks	FALSE
7	3 Wks	FALSE
8	27 Wks	FALSE
9	38 Wks	FALSE
10	2 Wks	FALSE
11	1 Wk	TRUE
Sheet60

Cell Formulas
Range	Formula
B2	=LEFT(A2,FIND(" ",A2)-1)+0<2

 

[Norie]

Try the Use formula to determine cells to be formatted option of conditional formatting with this formula:

=TRIM(LEFT(A1,LEN(A1)-4))+0<2

 

[JenniferMurphy]

Hi,

Use B2 formula as your CF formula:
[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell
[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=LEFT(A2,FIND(" ",A2)-1)+0<2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Of course. Doh! It never occurred to me to extract the numeric part. Thanks

 

[JenniferMurphy]

Try the Use formula to determine cells to be formatted option of conditional formatting with this formula:

=TRIM(LEFT(A1,LEN(A1)-4))+0<2

Of course. Double doh! This works perfectly. Thanks

 

[jtakw]

Of course. Doh! It never occurred to me to extract the numeric part. Thanks

You're welcome.

FYI, formula in Post #3 will error out if the value in Column A is like my sample A11 ( 1 Wk ).

 

[JenniferMurphy]

You're welcome.

FYI, formula in Post #3 will error out if the value in Column A is like my sample A11 ( 1 Wk ).

Got it.

How about this?

=(SUBSTITUTE(A1," Wks","")+0)<2

It works because I know that the units will always be exactly " Wks" even if the number is "1".

 

[jtakw]

Yes, that's great if you know it'll always be " Wks"

You don't need the extra brackets thou, =SUBSTITUTE(A1," Wks","")+0<2

 

[JenniferMurphy]

Yes, that's great if you know it'll always be " Wks"

You don't need the extra brackets thou, =SUBSTITUTE(A1," Wks","")+0<2

The extra parens are for readability (for me).