Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Adding preceding zeros to a number

Abdul Khan
edited 12/09/19 in Archived 2015 Posts

I have a column with 4 digits and want to add preceding 5 zeros. I can do single column with '00000 and the number, but I have to do for 4,000 rows and the number are different in every row. Is there a way or formula that can help me add preceding zeros.




  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭



    To be clear, the 4 digit number is in one colum, the 9 digit one is in another.

    If that is the case, and the 4 digit number is already padded (so the value is 0001, not 1) then




    should work for row 23.


    If you need to work about "1" instead of "0001", that's a little more formula, but not much.


    Hope this helps.






  • Hi Craig,


    Thanks for the formula, the four digits number in one column ex.1234 and these four digit number to be changed to  000001234 in the same column.


    Your formula works great when I create another column and I tried copying in the original column so that I can delete the new column but unable to do as the value change when I paste the copy.


    Do you have any other solution/formula that fix the four digit number with preceding zeros with the same number and same column? Or is there a way to copy from the new column where this formula convert from four digit to nine digit number.


    Thanks and appreciate your help.



  • Abdul Khan
    edited 12/02/15

    Hi Craig,


    I figured out copy and paste. I am using Paste Special (Ctrl+Shift+V) works.


    Please let me know if I can convert four digit number into nine digit by adding five preceding zeros.




  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭



    Unfortunately, I don't know a way to change the format of the number cell (like could be done in Excel) so that entering 1234 results in 000001234




  • Thanks Craig,


    Appreciate your help. I have created a new column next to the column which already have numbers and used the formula which added zeros to the existing number and used Copy and Special Paste to replace old four digit numbers with nine digit numbers and deleted the column with formula.




  • Travis
    Travis Employee

    Depending on what you need to do with the numbers, you could use a Contact List column instead of a Text/Number column which will not strip out 0's

  • Tim Meeks
    Tim Meeks ✭✭✭✭✭✭

    Travis,  a coworker of mine accidently discovered the Contact List keeping the leading zeros.. First I"ve heard of this. would be great to put in the Help Article for Contact LIst Column Type info.



  • Here is a formula to add preceding zero to make a number 9 characters long


    =RIGHT(1000000000 + [cell], 9)


    add 1000000000 (1 followed by 9 zeros) to the number. e.g. 4567 -> 1000004567

    Then take the right 9 characters of that number 1000004567 -> 000004567



  • Roland Horwood
    Roland Horwood ✭✭✭✭✭

    Ajay, good formula. Thanks.

  • I am looking for a way to show numbers after the decimal.

    For example we have versions of software 1.0  1.1  1.2  etc.

    But the 1.0 always appears as 1

    Anyone know how to format text/number column to show a number as text?

  • Ok, figured it out.  I was making it too hard.....

This discussion has been closed.