Conditional Formatting using the number from text and a number

[Peter Davison]

I have a formula -
=IFERROR(XLOOKUP(UNIQUE(FILTER(BE6:BE100,BE6:BE100<>"")),BE6:BE100,$BC6#&" - "&ROUND(BE6:BE100,0),""),"")
that returns text (column BC) and joins a number to it (column BE) then removes blank rows to get the list together
e.g. result as a spill

North West - 303

South East - 33

West Midlands - 156

Yorkshire and The Humber - 428

What I want to do is conditionally format each cell based on just the number part
So I tried using the following formula to extract the number in the "Format Values where this formula is true" section using
=RIGHT(BR6, LEN(BR6) - MAX(IF(ISNUMBER(MID(BR6, ROW(INDIRECT("1:"&LEN(BR6))), 1) *1)=FALSE, ROW(INDIRECT("1:"&LEN(BR6))), 0)))>(100+$BG$!)
Cell BG1 is a user number e.g. 20 so it should only format colour if the number is over 120
This doesn't work so I wondered if this can be achieved or not.
Any help would be appreciated.
Thanks

 

[jasonb75]

Perhaps use something like this, I haven't tested a spill formula in a cf rule so have no idea if it will work or not.

=UNIQUE(FILTER(BE6:BE100,BE6:BE100<>""))>(100+$BG$1)

 

[pjmorris]

Try this:

=RIGHT(BC1, LEN(BC1)-FIND("- ",BC1))*1>100+$BG$1

 

[Peter Davison]

Hi
This worked but also showed blank cells as formatted coloured
=UNIQUE(FILTER(BE6:BE100,BE6:BE100<>""))>(100+$BG$1)

This one worked and left blank cells blank
=RIGHT(BC1, LEN(BC1)-FIND("- ",BC1))*1>100+$BG$1

Thank you both for all the help

One further question if I may -

When you have a cell with text and a number like this

North West - 303

South East - 33

West Midlands - 156

Yorkshire and The Humber - 428

Is it possible to sort by the number?

Thanks

 

[Fluff]

Maybe

=IFERROR(XLOOKUP(SORT(UNIQUE(FILTER(BE6:BE100,BE6:BE100<>""))),BE6:BE100,$BC6#&" - "&ROUND(BE6:BE100,0),""),"")

 

[Peter Davison]

That's amazing something so simple added to my formula. I would have thought it would have sorted by the text but it has sorted by the number.
Thankyou for all your help.
I would tick to go green but it will eliminate the green tick above

 

[Fluff]

Glad we could help & thanks for the feedback.