Conditional formating where there are 2 values in a cell

Godders199

Active Member
Joined
Mar 2, 2017
Messages
299
Office Version
  1. 2013
Hello, before i start rebuilding a spreadsheet to split values into two cells, is there anyway to conditionally format a cell, based on one of the values contained in it. for example

11(78.6%)2(14.3%)1(7.1%)
<colgroup><col width="74" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2706;"> <col width="115" style="width: 86pt; mso-width-source: userset; mso-width-alt: 4205;"> <col width="89" style="width: 67pt; mso-width-source: userset; mso-width-alt: 3254;"> <tbody> </tbody>

the formula in the cell, counts occurance an then calculates the percentage.

all am looking to do is say if cell %> X then format red for example.

If not i will just split the formula into 2 cells and format both.
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,320
Office Version
  1. 365
Platform
  1. Windows
Maybe something like
=SUBSTITUTE(REPLACE(A4,1,FIND("(",A4),""),")","")+0>0.2

But it maybe be easier to format the cells by looking at the original cells.
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,902
Office Version
  1. 365
Platform
  1. Windows
Let's say that your entry is in cell D1, and you want to highlight it if it is greater than 10%.
Then you can use this Conditional Formatting formula:
Code:
=(TRIM(MID(SUBSTITUTE(SUBSTITUTE(D1,"(",REPT(" ",100)),")",REPT(" ",100)),100,10))*1)>10%
 

Watch MrExcel Video

Forum statistics

Threads
1,114,367
Messages
5,547,499
Members
410,797
Latest member
mlfuson
Top