Number column with leading zeroes

Jayy S.

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.

Paul.Woodward

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!

Jayy S.

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.

Paul Newcome

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?

Jayy S.

@Paul Newcome Yes, there are other entries that don't have leading zeroes and are numbers

Paul Newcome

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)

Jayy S.

@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.