Coda conditional formatting is buggy. What can I do?

1

To clean up commas, I need to regexReplace values from one column to another. Let’s not even go to discuss how frustrating it is that Coda does not support multiple international number separators, unlike Notion or SmartSheet, GoogleSheet or… But even if you get the format right with complex and cryptic REGEX formulas, the conditional formatting does not read the values right.

The format is to to get values below 0 and color the cell red. It is not humanly possible to see why this code breaks. I see the same format and the same number. It would only make sense if it somehow read the original comma seperated values and broke there, but I set the conditional formatting to look at the corrected column. It should read the same strings as the same numbers.

Screenshot 2024-01-21 at 18.13.43
Screenshot 2024-01-21 at 18.13.43712×912 15.1 KB

To transform comma-separated numbers from the original column, I use this formula:

If(RegexMatch(thisRow.[Data with commas],","),
  RegexReplace(RegexReplace(thisRow.[Data with commas],"\.",""),",","."),
  thisRow.[Data with commas])
2

Can you share your doc with me please.
Should be fairly easy to fix.
On a side note - you may as well just regexreplace all the time (unless you’re super critical of performance and you’re trying to avoid an unnecessary regexreplace)

3

It appears to me in the two columns where there isn’t any color highlighting, you have a “red tag” in the upper left corner of the first column indicating an error in a reference or formula, which is probably throwing off your cell coloring.

Once you eliminate those cell reference errors, your formatting should function correctly as with the upper rows (no reference errors in the 1st column).

Edit: Looking again it appears the currency symbol is missing in front of the -29 and -49??

4

hi @Andrej_Balaz1

In case you want to transform the numbers from EU to USA formatting, you can use the one below, whereby ‘theNumber’ is the EU formatted number.

thisRow.theNumber.SubstituteAll(".","").Substitute(",",".")

You put the columns on Currency values and that handles all the rest. More about the recent function SubstituteAll() in my blog.

I hope it helps for the time being, Cheers, Christiaan

5

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.