Formating Numbers

[Miguel_Gomez_Limmert]

Hi!
Let’s say I have a table that I download from my bank statement and want to organise the payments. Problem is that when copy pasting from my bank the format of the numbers is like follows (forces errors when runing f.e. “SUM”);

|Amount|
|—|—|—|
-950,00
-64,02
-14 078,00
-656,00
-2 096,00
-10 000,00

How do I run a formula in the row that;

• Removes the " - " in front
• Removes the space between f.e “14 078”
• Removes the last decimals and the coma “,00”

Thanks a lot for all your help!

[Quentin_Morel]

@Miguel_Gomez_Limmert

• Removes the " - " in front
• Removes the space between f.e “14 078”
• Removes the last decimals and the coma “,00”

Hi @Miguel_Gomez_Limmert

a good use case of basic chain formula in coda This is one of the MANY solutions you can use to solve this, not necessary the best as i’m no regex expert !

• Removes the " - " in front > split() the chain by “-” and take the last part
• Removes the space between f.e “14 078” > split the chain by blank space and join with NO space
• Removes the last decimals and the coma “,00” > split the chain by “,” and take the first part

Here you go. This does exactly what you ask, if you want to be more accurate/specific/generic, it may require some adaptations depending on the format of your source data

Hope this is ok for you

Q

[Pch]

I’m not a RegEx expert either but this seems to work :

thisRow.Name.RegexReplace("-|\s|,\d{2}","")

The Regular Expression "-|\s|,\d{2}" will match the “-” or (|) any whitespace (\s) or (|) the comma followed by any digits but exactly 2 (,\d{2})

[Quentin_Morel]

A few character earned in this formula length indeed Thanks @Pch
Let’s say that the split/join/last/first approach is more…“affordable” !

[Pch]

@Quentin_Morel

Let’s say that the split/join/last/first approach is more…“affordable” !

I completely agree !

It was just a small “just in case” (I’ve been scared of RegEx for a long time , so I’m trying to overcome this )

[Christiaan_Huizer]

hi @Miguel_Gomez_Limmert ,
I liked the contributions of @Quentin_Morel and @Pch very much. Like @Pch I hesitated a long time before I dove into the Regex Universe. Since this is actually an other syntax, I’d rather follow the path @Quentin_Morel explored. In the question the idea was to get rid of the last two digits

In my contribution (not a solution) below I keep them because in my perso context this would be more relevant.

cheers, Christiaan

[Miguel_Gomez_Limmert]

This is all great! Thanks all for the pointers!

[Quentin_Morel]

Happy to help, lots of use case for that kind of formula !
Cheers

[Christian_Antonsen]

This was a very easy to understand solution. Needed this one for formatting numbers from “norwegian system” to “english/international”. This was a perfect fit to change the decimal point from “,” to “.”.

Which again reminds me, perhaps this could be an optional setting within Coda, define your decimal character, and perhaps it could be set as a document standard… Just a thought.

[Quentin_Morel]

@Christian_Antonsen

This was a very easy to understand solution. Needed this one for formatting numbers from “norwegian system” to “english/international”. This was a perfect fit to change the decimal point from “,” to “.”.

Thanks @Christian_Antonsen, it has the advantage to see “in chain” what we’re doing.
Also Hope coda’s working on “document standard” for currency, date format, etc…