Adding preceding zeros to a number

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

Find more posts tagged with

Comments

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

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

Abdul Khan

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

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

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

Travis

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

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

Ajay Jindal

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

Ajay, good formula. Thanks.

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?