How can I add zeros to the beginning of a list of numbers in Excel?

Question

for example I have a column of numbers :
12345
12546 etc.
and I need it to look like
00000012345
00000012546

Is there a way to do this beside the obvious of typing as there are thousands of them?
Help appreciated thanks!

Answer 1

Select the column of numbers that you want this 00000 format on.
- Right click
- Select "Format Cells"
- In the "Number" Tab select "Custom" in the "Category"
- In the box under "Type:" enter the following "000000#" (enter the number of zeros that you want before your number; do not include the quotation marks)

Note: if you want say six 0's in front of your number then enter six 0's, however if your number is double digit, only four 0's would show up. So your column would look like this:

e.g.
0000001
0000002
.
.
.
0000015
.
.
.
0010104

If you want the number of zeros to stay the same regardless of how many digits your initial number is (i.e. there are six 0's in front of 1 and there's six zeros infront of 100), then follow the same steps as above but type in the "Type:" box the following (including the quotation marks): "000000"#

Answer 2

Here's what to do. Click on the first cell you want changed. Select Format. Select Cell. You should be in the Number tab. Select "custom" from the category list. Type in 12 zeros in the "Type" box. (You can see what it will look like in the Sample at the top.) Click on OK.

First save the file so you don't accidentally mess it up. Now you just have to copy and paste the formatting. Select the cell that you just made the above change to. Hold down Ctrl and press the C button on your keyboard (Ctrl C copies the info). Highlight the rest of the column where you want to paste the formatting. Then select Edit and select "Paste Special". Click on the button beside Formats (under the Paste section). That's it.

Answer 3

Use '0000123434
The apostrophe ( ' ) will tell Excel anything that follows it is litertal.

Ok listen up leave it in NUMERIC format the apostrophe ( ' ) will still allow numeric fomulas, Then you dont have to convert back and forth between text!

Dont make more work for yourself....

Use EDIT, REPLACE 000000 with '000000

Done.

Hope this helps

My bad I thought you already had the number in the 000001234 format but the 0's where not showing up....

My apologies

They are correct below, Custom formatting will add as many 0's in the field as you want, provided you want a set number of digits that will not change.

Answer 4

Right click on the cell, and select format cell. Select Custom at the bottem of the list, and type in your (or how ever many numbers you need) 11 "0s", then copy that cell into each cell you want that way. From there on just enter in your 5 numbers, and it will autoformat itself to include the 6 0s at the beginning.

From there you can tell it how many decimal spots you want, among other things...

Answer 5

Select/highlight the colum you want to add the zeros to. On the menu bar, select >Format >Cells, under the Number tab, select "text".

Answer 6

1st Mehtod
Select the area
Format
Cells - Number
Custom
When you go to custom, type in that box 00000000000 (no of digits you want in the cell)
Then it will automatically fill the zeros in front

2nd Method
=CONCATENATE(REPT(0,11-LEN(G4)),G4)

assumed 11 is the no of digits you want. you can change it.
Other parts of the formula is ok except the cell reference (G4)
Change it & put your reference.

What other answerers are saying is also ok. But becuz you have a large list you have use one of these 2.

Answer 7

Right click on the cell, go to FORMAT CELLS, and select TEXT from the list on the right

Answer 8

->format cells
-> Custom
-> Type: (enter your 6 0's in the box)
-> ok

Answer 9

Kala, turn them into TEXT, so you can pad them with zeros, when you need to calculate them, add the formula to convert them to numeric before the calculation.