# Microsoft Office Application Help - Excel Help forum > For Other Platforms(Mac, Google Docs, Mobile OS etc) >  > [SOLVED] 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  :Smilie: 

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... :Confused: 
(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  :Confused: )

*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.  :Frown:

----------


## 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!!!!  :Wink:  :Smilie:

----------

