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

 

Thanks

· Share on FacebookShare on Twitter

Comments

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

    Abdul,

     

    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

     

    ="00000"+[4digitcolumn]23 

     

    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.

     

    Craig

     

     

     

    · Share on FacebookShare on Twitter
  • Abdul Khan
    Abdul Khan ✭

    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

    · Share on FacebookShare on Twitter
  • Abdul Khan
    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.

     

    Thanks

    Abdul

    · Share on FacebookShare on Twitter
  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    Abdul,

     

    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

     

    Craig

     

    · Share on FacebookShare on Twitter
  • Abdul Khan
    Abdul Khan ✭

    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.

     

    Thanks

    Abdul

    · Share on FacebookShare on Twitter
  • 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

    · Share on FacebookShare on Twitter
  • 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.

     

    Tim

    · Share on FacebookShare on Twitter
  • Ajay Jindal
    Ajay Jindal Employee

    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

     

     

    · Share on FacebookShare on Twitter
  • Roland Horwood
    Roland Horwood ✭✭✭✭✭

    Ajay, good formula. Thanks.

    · Share on FacebookShare on Twitter
  • gregjohnsondsm43126
    gregjohnsondsm43126 ✭✭✭✭

    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?

    · Share on FacebookShare on Twitter
  • gregjohnsondsm43126
    gregjohnsondsm43126 ✭✭✭✭

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

    · Share on FacebookShare on Twitter
This discussion has been closed.

Categories