Number column with leading zeroes
I need a way to keep leading zeroes for numbers in a column, but also keep them in number format for sorting purposes. These are test codes so leading zeroes are necessary but Smartsheet kept converting them into text.
Answers
-
Hello Jayy
There is not a lot of information to go off of but you should see the "ZERO" options in your tool bar (see below), to increase or decrease the amount of Zero's displayed in a cell. You can then add it to the entire column/row, so any # you add in has the amount of leading Zeros needed.
You could also add a new column that is "# Auto Number" Where you can set the display format for prefix, numerical places, and starting #'s. See below.
Please let me know if there is anything else I can do to assist!
-
Unfortunately, no. The "zero" options are for decimals, not leading zeros. It's also not an auto-number column as these codes are assigned and we need to track them in SS.
-
Leading zeros shouldn't cause any issues with sorting. Or do you have some entries that do not have leading zeros and are being treated as numbers?
-
@Paul Newcome Yes, there are other entries that don't have leading zeroes and are numbers
-
In that case you will need a helper column. You can convert all to text using
=[Column Name]@row + ""
(plus quote quote)
Or all into number values
=VALUE([Column Name]@row)
-
@Paul Newcome I've tried the VALUE formula, but that removes all the leading zeroes. Convert them all to text makes it sort incorrectly. I tried that and sorting in ascending order gave me 89xxxxx and then 98xxx.
