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

Options
edited 12/09/19

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

• ✭✭✭✭✭✭
Options

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

• Options

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.

Abdul

• edited 12/02/15
Options

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

• ✭✭✭✭✭✭
Options

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

• Options

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

• Employee
Options

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

• ✭✭✭✭✭✭
Options

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

• Employee
Options

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

• ✭✭✭✭✭
Options

Ajay, good formula. Thanks.

• Options

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?

• Options

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

This discussion has been closed.