Coding Decimals in Coda
a comma instead of a full stop
The good news you don’t have to read this blog. The issue is solved by by AI already. Bill showed this.
The interesting part however is that although AI is very good, it is not fully deterministic. With great prompts it will deliver close to perfect solutions, but maybe not perfect enough in case you need to be a hundred percent sure.
This blog is for the small group of people who love Coda AI, but need some guarantee that the outcome is reliable. In other words, they want to put in place checks and balances.
This blog contains the complete code snippet to turn USA numbers with a comma into a European number format.
The coded solution
It is partly based on the logic I developed last year september.
To make the steps easier to follow I created a table that shows per column the steps we bring together in the column output:
We have the input and we split the number in the decimals and in what I call the main number, the string before the dot. Others would call it an integer.
When you are looking for an easy copy — paste, here you go. Make sure that the name of the column is input.
SwitchIf(thisRow.input.Find('.') >= 1,
thisRow.input.Slice( -(thisRow.input.Length() - thisRow.input.Find('.')))
).WithName(decimals,
thisRow.input.Slice(1,thisRow.input.Find('.')).Split('').Filter(CurrentValue.IsNumber()).Join('').WithName(mainNumber,
mainNumber.Length().Remainder(3).WithName(start,
sequence(start,mainNumber.Length(),by: 3).WithName(rightPart,
Listcombine(1,Sequence(1,rightPart.Count()-1).ForEach(rightPart.Nth(CurrentValue) +1)).WithName(leftPart,
Sequence(1,rightPart.Count()).ForEach(
mainNumber.Slice(leftPart.Nth(CurrentValue),rightPart.Nth(CurrentValue))).Filter(CurrentValue.IsNumber()).Join(".").WithName(outcome,
SwitchIf(
decimals.IsBlank(),outcome,
decimals.IsNotBlank(),
Format("{1}{2}{3}",
If(mainNumber > 3,outcome,mainNumber),
Concatenate(","),
decimals),Concatenate(mainNumber,",",decimals))
))))))
The decimals
We know that decimals in the USA notation is found after the dot. We get the dot via Find(). Once we find a position greater than one, we have a decimal. All we need to do is to evaluate the complete number and slice it on the decimal. That happens in a few steps. First he function Length() returns the number of characters in the string and thus the max positions. By subtracting the position of the dot — we got via Find() — from the Length() we know where to slice the number using a negative number by adding a minus symbol in front it. This results in a named function ‘decimals’ holding only the decimals. May it be unclear, via a minus operation we slice values from the ‘end’.
SwitchIf(thisRow.input.Find('.') >= 1,
thisRow.input.Slice( -(thisRow.input.Length() - thisRow.input.Find('.')))
).WithName(decimals,The main number
This is actually a rather easy operation based on the previous work. We intent to get the numbers living in front of the dot and if there is no dot, we take all values. We use Find() again to Slice() This time from position one onwards. Via Split() we turn the string into a list and we filter this list on numbers to finally glue the list together as a string with Join().
thisRow.input.Slice(1,thisRow.input.Find('.')).Split('').Filter(CurrentValue.IsNumber()).Join('').WithName(mainNumber,Replacing commas with dots
As from here, you need to let your imagination speak. The idea is to cut the main number into pieces of 3 digits to allow us to replace the commas. We solve this without Regex, because this would require an other and extra syntax to learn. We solve the problem with the Coda formula language.
We need to know where to start and thus where to put the first dot. For this we use the function Remainder(). It shows the left overs after a number is divided, in our case divided by 3.
mainNumber.Length().Remainder(3).WithName(start,
sequence(start,mainNumber.Length(),by: 3).WithName(rightPart,The next step is to use this number as a position in a sequence in which the length of the main number is the number we go to in steps of 3. The latter we got using by: 3. The result is something like 1,4,7,10 ín an string with 10 digits (the decimals excluded). We call this the rightPart for reasons you see soon.
We also need a leftPart, because we want to slice the main number into blocks of max 3 digits. And to slice we need a “from” and “to”. This is the from part living on the left side in the function. Details about this logic here.
Listcombine(1,Sequence(1,rightPart.Count()-1).ForEach(rightPart.Nth(CurrentValue) +1)).WithName(leftPart,With left and right we create pairs like below:
1–1, 2–4, 5–7, 8–10
Once we have the two variables for the Slice(), we start slicing the main number and once done, we make sure we are left with only numbers to finally glue the numbers together with a dot via Join().
` Sequence(1,rightPart.Count()).ForEach(mainNumber.Slice(leftPart.Nth(CurrentValue),rightPart.Nth(C.IsNumber()).Join(".").WithName(outcome,We finish this code snippet by bringing all elements together using a SwitchIf(). We let first check on decimals and when we have decimals we make a choice in the Format() function with an If()
SwitchIf(
decimals.IsBlank(),outcome,
decimals.IsNotBlank(),
Format("{1}{2}{3}",
If(mainNumber > 3,outcome,mainNumber),
Concatenate(","),
decimals),
Concatenate(mainNumber,",",decimals))Once you have the main peaces this follows with a bit of trial & error. So far the hardest part was to get the left & right values in the Slice() and to find a way to slice the main number (using a virtual index).
Back to USA Format
The other way around is easier and thus the code snippet is shorter:
thisRow.output.Find(',').WithName(theDot,
If(theDot = -1,"",
thisRow.output.Slice( -(thisRow.output.Length() - theDot))
).WithName(decimals,
thisRow.output.Slice(1,theDot).Split('').Filter(CurrentValue.IsNumber()).Join('').WithName(mainNumber,
Format("{1}{2}{3}",
mainNumber,
If(decimals.IsNotBlank(),".",""),
decimals)))).Trim()Do we need this?
As I wrote before: not really, you can have an Ai doing this for you. You can use parts of the code snippet to check if the AI is doing well. Quite likely it is, but better safe than sorry. I would recommend the part relating to the length of the sting for example, that is a relative good to follow code snippet, see below:
`thisRow.input.Find('.').WithName(theDot,
If(theDot = -1,"",
thisRow.input.Slice( -(thisRow.input.Length() - theDot))
).WithName(decimals,
thisRow.input.Slice(1,theDot).Split('').Filter(CurrentValue.IsNumber()).Join('').Length() ))Coda AI did it (again)
As mentioned before, the AI solution Bill showed is in my opinion the best way forward. No need for complexities as shown above. A well written prompt will do the job for you.
This is also the case for an other challenge. We are able to get the duration of a period expressed in years, months, weeks, days, hours and even minutes based on a smart prompt under the conditions you execute in English. In Dutch I noticed however a few mistakes.
Does this make coding Coda reluctant? No it does does not. In the example of the duration, we apply testing rules to check if the AI performs well. This set up requires some deep understanding of how to Coda. The AI helps you to move on faster, but only if you know how to Coda.
More details in this blog:
My name is Christiaan and blog about Coda. Since the summer of 2023 mainly about how to Coda with AI to support organisations dealing with texts and templates. Why I focus on Coda AI you can read here: ⤵️
I hope you enjoyed this article. If you have questions feel free to reach out. Though this article is for free, my work (including advice) won’t be, but there is always room for a chat to see what can be done. You find my (for free) contributions to the Coda Community and on Twitter.
Coda comes with a set of building blocks ー like pages for infinite depth, tables that talk to each other, and buttons that take action inside or outside your doc ーso anyone can make a doc as powerful as an app (source).
Not to forget: the Coda Community provides great insights for free once you add a sample doc.
