Column Causing Sorting Issues

April G

Hello - I am the Admin for a SS that has 40+ users. One of our columns is critical for our unique identifiers for jobs #s. Instead of asking people repeatedly to be sure to make job 1-9 be 01, 02…, I would like to make the existing column have a proceeding 0 so for sorting the lines will sort properly. They currently are sorting as seeing the 01, 02, etc as a 100 spot. So my lines sort as

48

49

50

02

04

05

This is throwing me off when looking for job 3 in the account of 123456789 as it is in the bottom of the sort instead of the top. Is there a way to format this column to always be 2 digits and ensure all jobs entered prior to 10 will have a proceeding 0?

Thanks!

Kerry St. Thomas

The first thing that leaps to my mind is going the Helper Column route. In the column that exists now, llet users number as 1, 2, 3, … and so on, without worrying about the leading zero. But then create a helper column that throws in the leading zero - and then formulate your unique job numbers with a formula along the lines of:

=IF(VALUE(JobNumber@row)<10,"0","")+VALUE(JobNum@row)+AccountNum@row

The VALUE exists in this in case people enter '06 (with the apostrophe) as a workaround to ensure they get the leading zero, to convert it into a number so there's no question about whether it's smaller or larger than 10. The quotes at the beginning will make Smartsheet convert the output back to a text value, so it'll concatenate everything you don't end up doing the math of 0+9+123456789 or whatever. When I need a leading zero, I use this process instead of the JOIN formula.

Good luck!