Remove leading zeros from date?

Hi…
I have dates in a column that are in two different formats - either with leading zero (041721) or without leading zero (41721). I either need to remove the leading zero where present, or add it where it’s missing because Coda can’t sort the column when it’s a mix of leading zero/non-leading zero.

Two questions:

For Coda, is it better to include or remove the leading zero?
And is there a fast way to add the zero or remove it?

Many thanks!

Gregg

Hi @Gregg_Stebben :blush: !

Would you mind sharing a sample of your setup ? :blush:

It will be easier to see what you mean and help you :innocent:

1 Like

I think the fastest and bulletproof way to get a non-leading properly sortable number is to apply .ToNumber() to it, e.g. ToNumber(01234) → will certainly give you a number.

For the completeness sake, to add leading whatevers to whatever you can use LeftPad(), e.g. LeftPad(12345, 6, 0). It will always produce text (not number) so the sorting will obey the alphabet. The first parameter (the input) and the third one (the padding) are converted to text, so the above is identical to LeftPad("12345", 6, "0")

P.S. It’s better to sort numbers than text values. Sorting text values means comparing them character by character until the first mismatch (each character is basically a number) while sorting numbers is always one compare operation per the pair of numbers.

3 Likes

Hey @Pch
Thanks for your note…is this what you’re asking about when you say “sharing a sample of my setup”:

image

Basically, I have a lookup with a column that includes date + T (Top) or + B (Bottom). This info gets pasted in weekly from Google Sheets and the date +T or +B is the date the info is pasted into Coda. In the beginning, I used dates without the leading zero and then without realizing it started pasting in the date with the leading zero, and that’s creating sorting problems, so I either need to add a leading zero or remove it!
image

Take a look below! If it doesn’t perfectly match your use-case it should give enough direction and ideas to solve your issue

1 Like

That’s awesome! Thank you!

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