Conditional formating where there are 2 values in a cell

Godders199

Active Member
Joined
Mar 2, 2017
Messages
285
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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,454
Office Version
365
Platform
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
51,157
Office Version
365
Platform
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%
 

Forum statistics

Threads
1,078,309
Messages
5,339,410
Members
399,302
Latest member
Swiftymj

Some videos you may like

This Week's Hot Topics

Top