Formula fails after 1000 rows

Have this formula that works like a dream until I get to 1000 rows

It basically creates a sequential number like this DM-0998, DM-0999.

="DM-" + IF(One@row < 10, "000", IF(One@row < 100, "00", IF(One@row < 1000, "0", One@row))) + JOIN(COLLECT(One@row:Two@row, One@row:Two@row, ISNUMBER(@cell)), ".")

Once we hit 1000 rows the formula results in this. DM-10011001


This formula throws an invalid operator error instead of DM-1001

="DM-" + IF(One@row < 10, "000", IF(One@row < 100, "00", IF(One@row < 1000, "0", One@row, IF(One@row < 1000, "", One@row)))) + JOIN(COLLECT(One@row:Two@row, One@row:Two@row, ISNUMBER(@cell)), ".")

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!