Google Sheets: conditional formatting for cell containing letters and numbers

[jher001]

Good day experts,

I would like to ask how to change the background color to red when a cell contains letters and numbers and the number is above 120 and change the color to green when the number is below 120.

[kev_]

The solution below is based on the values provided in post#1
- this solution is only correct if those cells containing both letters & numerics are constructed [2 letters followed by space followed by numerics] (as in your sample data)

CF formula
=IF(ISNUMBER(A1),"Number only",IFERROR(IF(SEARCH(" ",A1)>0,IF(TRIM(SUBSTITUTE(A1,LEFT(A1,2),""))*1>120,"Red","Green")),"Text only"))="Green"

=IF(ISNUMBER(A1),"Number only",IFERROR(IF(SEARCH(" ",A1)>0,IF(TRIM(SUBSTITUTE(A1,LEFT(A1,2),""))*1>120,"Red","Green")),"Text only"))="Red"

The logic of the formula
- is A1 a number? (we can ignore if A1 only contains a number)
- if A1 is not a number, does it contain a space? (we can ignore if A1 does not contain a space because it contains only non-numerics or is empty)
- if A1 is neither of the above it contains both
- if A1 contains both, extract its numeric element and test against the 120 hurdle

Why A1?
When applying CF to a range of cells, the formula used is the one that would be correct for the top left cell in the range
- CF was applied to (entire) columns A & B and so the formula used is the one applicable to cell A1
- columns D & E in attached workbook contain formulas that mimic what is used in the 2 conditional formatting rules so that you can see how it works,
- they are not part of the solution and can be deleted after testing

Sample data
- the attached workbook contains your values and returns the requested results

CF rule58.jpg


Does not work with live data?
If this does not work with your some of your real data, please attach a workbook with typical data that covers every eventuality
Remember to desensitize the data.
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[jher001]

Thanks kev_. Works perfectly! You're amazing!

[jher001]

Hi kev_,

Follow up question. In the formula that you have provided (which is working really great) how do you prevent cells that contains two letters and 0 from changing colors.

Thank you.

[kev_]

Hmmm...
(Untested) Perhaps insert an extra condition here (this is only part of the formula)
IF(TRIM(SUBSTITUTE(A1,LEFT(A1,2),""))*1>0, IF(TRIM(SUBSTITUTE(A1,LEFT(A1,2),""))*1>120,"Red","Green"))
but that seems a bit messy for one exception (I will try to think of a more elegant solution )

Are there any other "funny" values?
If there are send me a workbook with typical values

.

how do you prevent cells that contains two letters and 0 from changing colors.

Is there a space?

[jher001]

Yes, there is still a space. I just want to prevent DF 0 and GH 0 from changing colors. Sorry for the additional problem.

[kev_]

In that case we can wrap a simple additional condition around everything checking to see if the last 2 characters of the cell are space followed by zero

=IF(RIGHT(A1,2)=" 0","Text and Zero",IF(ISNUMBER(A1),"Number only",IFERROR(IF(SEARCH(" ",A1)>0,IF(TRIM(SUBSTITUTE(A1,LEFT(A1,2),""))*1>120,"Red","Green")),"Text only")))="Green"

Same for "Red"

[jher001]

Thanks, kev_. Works perfect! You're the best!!!!