Number formats - Column as Text vs. Numeric

I formatted the column before adding to Grist. I need leading zeros for UPC Numbers.

Making the column type in Grist as Text keeps the leading zeros, but turns values over 13 characters into a scientific notation.

If I change the column type to Numeric, the leading zeros are gone…

Text columns in Grist will not transform the text when you enter it, so it’s the best choice here. If you enter your UPC Numbers directly into a Grist Text column (by typing, or pasting, or via API), they would neither lose zeros, nor get converted to a scientific notation.

The scientific notation must have come during import or conversion of column type. Did your data get imported from Excel? If so, you can force values in Excel to be pure text by entering them with a leading single-quote character.

Finally, if you do have incorrectly-formatted numbers in Grist, you can format them correctly by adding a formula column with this formula:

"%013.0f" % float($A)

replacing $A with the name of the column containing incorrectly formatted values. You can also replace the “13” in the formula with the desired length to pad to (e.g. 013 above pads the result with 0’s on the left to 13 characters total).

This sounds like a repeat of Mixed Column Data Types - converts data incorrectly . Did my solution there not work?

@dmitry-grist the problem is a large float in a numeric column is formatted by Grist as scientific notation when converting to text. Might help if the type was guessed as integer instead of numeric.

2 Likes

Padding the column with the float formula worked on the Numeric Column! :smiley:

2 Likes