Formatting Cells

sandramac
sandramac used Ask the Experts™
on
Hello, I have a row of cells from b10:n10, that has data in this type of format 170 12G23MPH or 170 15MPH.  I need to evaluate the last two digits before MPH.  If between 35-50, then backfill that cell with color orange, if greater then 50 then backfill color red, else is green backfill color.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Sandramac if you have Excel 2007 or later:

Go to Ribbon then "Conditional Formatting" then "Highlight cell values" and use appropriate equations.

Author

Commented:
What would be the appropriate formulas?
Does your cell look like this literally ? "170 12G23MPH"
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

There is "Between" formula and ">"  than (50) formula that you can use. If your data look is popluated in one cell like I described above you may consider splitting it into columns first using text to colums option in Excel.
Try this formula

=VALUE(IF(ISERROR(VALUE(MID(REPLACE(REPLACE(B2,1,FIND("MPH",B2)-4,""),4,999,""),2,1))),RIGHT(REPLACE(REPLACE(B2,1,FIND("MPH",B2)-4,""),4,999,""),1),IF(ISERROR(VALUE(MID(REPLACE(REPLACE(B2,1,FIND("MPH",B2)-4,""),4,999,""),1,1))),RIGHT(REPLACE(REPLACE(B2,1,FIND("MPH",B2)-4,""),4,999,""),2),REPLACE(REPLACE(B2,1,FIND("MPH",B2)-4,""),4,999,""))))<35

changing the last bit according to your ranges. This particular formula is with reference to cell B2.
Hi, sandramac.

The attached needs Excel 2007+ - please let me know if you're on an earlier version.

The conditions are...
No Format --> =IFERROR(MID(A1,LEN(A1)-2,3)<>"MPH",TRUE)
Red            --> =MID(A1,LEN(A1)-4,5)>"50MPH"
Orange       -->=MID(A1,LEN(A1)-4,5)<"35MPH"
Green         -->=TRUE
(First three conditions are "Stop if True".)

Regards,
Brian.MPH.xlsx

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial