Mixed Column Data Types - converts data incorrectly

I have a column of Model Numbers, with mixed data types. Grist auto assigns the column as Numeric Values.

  1. Non Numeric values have preceeding zeros
  2. Changing the data type to Text turns numeric values into scientific notation.

1 Like

This is definitely a bug that needs fixing, thanks for letting us know. Here’s what you can do in the meantime:

  1. Create a new column. Unfortunately “Apply formula to data” doesn’t seem to work in this case.
  2. Enter this formula in the new column: IFERROR(str(int($Model)), str($Model))
  3. Under Column settings, set the type to Text, then next to “FORMULA COLUMN”, click ACTIONS > Convert to data column.
  4. Now you can delete the original column and rename the new one.

That’s mainly to solve the scientific notation problem. For the preceding zeroes, that’s really weird, do you know what did which led to that? If you want to remove all preceding zeroes in the data, add .lstrip('0') to the end of the formula in step 2. You can also use similar code in a column transform later on.

1 Like