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
Comments

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

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

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

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

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

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

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

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

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