I have a Text column called Serial and I use it to keep serial numbers such as 0001, 0002, 0003
Then I need another formula column where I take the Serial column and add stuff to it, such as “0001 Apple”, “0002 Orange”. Let’s say the formula is Serial + " " + Name.
This will produce “1 Apple”, it omits the leading zeros.
After some googling, I found the LeftPad function, which by itself works. Searial.LeftPad(4,“0”) produces “0001”. However, when i start using the + operator, like: Searial.LeftPad(4,“0”) + " " + Name
It only returns “1 Apple”.
the reason we think the ‘+’ operator is the problem is because it often turns strings into numbers (if it can) and then try to do a numeric ADD operation. This is why your ‘00001’ i(a text string) is being turned into ‘1’ (an integer). The LeftPad() function is also returning ‘00001’ but that is also being turned into a number by the ‘+’ operator.
so to remove all this conversion confusion, use the Concatenate() function which just concatenates text strings without any conversion to numerics.
Concatenate works. I’m just bothered by the fact that LeftPad() already returns a string, so the + operator can simply take the result and concatenate. The fact that + turns the result of LeftPad() into into a number again and then back to string before the concatenation happens seems like a bug to me.
The primary purpose of the ‘+’ is to do arithmetic addition of two numbers.
So if it find one of its operands is a NUMBER but in the form of a TEXT, it converts it first.
So turning ‘00001’ (text) into ‘1’ (integer) is expected behavior.
The secondary purpose of the ‘+’ operator is to concatenate two texts.
So if ONE of the operands is text that cannot be converted into numbers it will concatenate them.
When an operator has several operations depending on the type of its operands, we call that OVERLOADING. And it can be confusing for humans, because the rules the computer uses to decide which operation to choose are not always the rules we expect using intuition.
This is made even more confusing because Coda very helpfully converts one data type into another automatically (called CASTING) to try to help make formulas simpler and easier to write.
When you have the combination of OVERLOADED operators and automated CASTING of operands, the results are sometimes NOT what the user was expecting. Is that a BUG? Maybe.
I think the double whammy of converting to number first and then doing concatenate IS a bug.
In this case I think Coda was unwise to overload the ‘+’ operator like this. In Excel and Sheets we use the ‘&’ symbol for text concatenation - so there is never any confusion. But in a lot of other programming languages, the overloading of the ‘+’ operator for addition and concatenation is common, so I suppose the Coda engineers were just used to that way of thinking.
The final upshot of all of this is
(1) only use the ‘+’ symbol for concatenation if you are SURE both operands are text
(2) preferably use X.Concatenate(Y) or Concatenate(A, B, C) forms for maximal clarity