I have a column with values that look like this:
**
N/A
UV+147%
UV+85%
UV+24%
UV+29%
**
I would like to apply a (color) style to cells where the positive numeric values are above say 75. It’s text so cell > “UV+75” works up to UV+99% but not above. If I try comparing cell > “UV+100%” it highlights UV+11% because of the ASCII values. I suspect I need to use a REGEX function and formula, but nothing I’ve tried works. Suggestions?
Welcome!
May be so =VALUE(REGEX(A1;"\+\d*"))>75?
Thanks, @JohnSUN – however, it also doesn’t work for me. I had tried some other REGEX expressions that also didn’t work, though I don’t remember now what they were – sorry. It’s a mystery to me!
If the cells were numeric but formatted to have the same appearance then you could do it in Format Cells, [RED][>0.75]"UV""+"0%;"UV""-"0%
See Conditions in Number Format Codes
To convert the existing cells format them as above, select the column and use the Find and Replace method, How to convert text to numeric data
Thanks, @EarnestAl – alas, they cannot be simply numeric. By way of explanation, these strings represent value forecasts, so “UV” is undervalued and the number following is the forecasted gain. There are cells with strings such as “OV+10%” which represent an overvalued item but still with a forecasted gain, but which would not be desirable to highlight the same way. (Currently, I’m leaving cells with the above OV+ status as neutral, but negatively highlighting any with “OV-xxx” status.)
I really appreciate the suggestions, y’all!
@JohnSUN formula worked for me until new condition of “UV” only was supplied. My poor understanding of REGEX means I have to extend the conditional formula to AND(VALUE(REGEX(A2,"\+\d*"))>75,LEFT(A2,2)="UV"), probably there is a more elegant formula to replace my addition.
UV_greaterThan75.ods (10.9 KB)
Is it an ODS file with regex enable? Menu/Tools/Options/LibreOffice/Calc/Calculate - Enable regular expression in formulas. If the file is a XLSX then by default wildcards are enabled.
Just a thought (or three):
Calc, like most other data manipulation/analysis tools I know, works best with atomic values (single item; one dimensional). Your column holds a compound value: we can call the individual items Valuation state and Forecast. You can easily extract those two elements as atomic values in separate columns, either inserted in the same sheet where you are working, or by adding a separate sheet.
This would make it easier to apply the desired formatting, and would also otherwise facilitate more efficient analysis of the data. For visual purposes, the additional columns can be hidden or positioned away from your primary data if needed.
It’s probably worth trying this:
VALUE(REGEX(A2;"^UV(\+\d*)";"$1"))>0.75
VALUE(REGEX(A2;"^OV(\-\d*)";"$1"))<0
Sorry for my bad „denglisch“ conversation!
You have to split any inserted strings into text and numeral, while text like „8a“ cannot be calculated with math. notifications however a digit-string do it.
CALC do automatically transform a native digit-string like „853“ into the math. number =853 though you havn’t format this cell by text.
A solution for you shows an adaptive from „UV+5%“ into „UV+005%“ which can correctly decide between lower and higher include eventually a comma/decimal point.
Any Number (digit-string) you can expand to the same numbers of decimal places by adding in front by zeros (Nullen). So this serve the math./μC-purpose for all.
see @keme1 :
the best way with easy calculations and ratings is to separate any komplex value/string in extra cells, for example: either text „UV“ or „OV“ in the 1st cell, the numeral in the 2nd and the text unity (Km, Sec, MJ, %, ppm, …) in the 3rd.
one probe:
1_LibreOffice-CALC_STUDiE_berechenbare spezielle Zellenformate_iN ARBEiT-v0001.ods (28.1 KB)
1_LibreOffice-CALC_STUDiE_berechenbare spezielle Zellenformate_iN ARBEiT-v0001.pdf (119.6 KB)
I hope you have a little deeper view onto μC-operations and -functions with aid of my short excursion.
Thanks, #JohnSUN #EarnestAI #mariosv @keme1 @koyotak – I appreciate all the suggestions! I tried most of the suggestions – regular expressions were not enabled, but enabling them did not change the results for me, with any expression I tried. I did not want to go with the division of the values into alpha and numeric only values. The final suggestion to add the leading zeroes to the number part of the text string worked, the Occam’s razor in this case. It was a bit of a pain to have to add the zeroes, but at least it works now. Thanks again! (Sorry, “new users can only reference up to two users in a post”, hence the #user stuff.)
I am still mystified as to what exactly regular expressions can do in Calc. So far, it’s been a bust. I might have to keep playing with this and report if I find any successful expressions.