How do you add a leading zero to a column of cells in an Excel file?

Question

I have an Excel file, and in one column there are ZIP codes. Unfortunately, when it's opened in Excel, ZIP codes which begin with 0 lose the leading 0. What's the best way to automatically add back the zero to all those cells (without going through them one-by-one)?

I can save the spreadsheet as a CSV, then make a script that will add the 0 automatically, but then when I open it up in Excel again I have the same problem.

Answer 1

Select all the cells that you want to put zip codes in (or select the entire column and/or row), now right click on them and select "Format Cells". From the category list box click on "Text" and press ok. Anything you enter into the cells will now appear exactly as you enter it but will still work with formulas.



If you've already entered the zip codes then follow these same instructions but instead of clicking on "Text" click on "Custom" and enter the string "00000" into the "Type" text box i.e. the bit that by default says "General".

Answer 2

1

Answer 3

Add Leading Zeros In Excel

Answer 4

The easiest way to do this is:



1) Highlight the cells in question.

2) Choose Format Cells/Number Format.

3) Choose Custom (the default is General)

4) In the "type" box, put 00000. This tells Excel to always force five digits, by adding leading zeros if necessary.



Also, in the latest version of Excel there's a pre-defined format under "Special" called "Zip Code," but I'm not sure that this is in older versions of Excel.

Answer 5

First I would sort the entire list by zip number. This should give you a numeric list that is accurate. Click on the column and format as text. Next I would create a new column and preformat it as text. Enter the data into the column such that you have 0000 as far as you need down the column, then 000, then 00, then 0. Next, concatenate



=concatentate(b1,a1)



Where b1 is the 0's and a1 is the remainder of the number. Drag this down the column until you reach the end of the 0's. Format the column as zip. Format the original column as zip. Copy the concatenated column and paste special>values to the original column. Delete the calculated columns and 0's columns. Format an empty column as Custom>00000. Be sure to include 5 0's. Copy the original reformatted columns and paste as number for calculations.





God Bless



Frank Pytel

Answer 6

Step 1: Enter the formula =Text (A1, "00000") in a blank cell (the number of 0s you use is how many numbers it is supposed to be, so if mine should be 9 digits, but is only 8, I enter the 9 0 s and it will then add the leading 0 to my 8 digits) which is adjacent to the data cell.Step 2: Then press Enter key, and select cell C1 drag the fill handle Description: Selected cell with fill handle across the range that you want to fill. Then you will view all the numbers in A1:B6 are copied and pasted to C1:D6 with leading zeros. And each numbers contains 5 digits.

Answer 7

You could do it either one of two ways. One is to select the cells and go to "Format" and then "Cells" (i.e. Ctrl-1). Under the "Number" tab, there's an option called "Special", and you can set the cells to ZIP code mode right then and there. It should automatically set those cells needing a leading zero to the correct format.



Another way is to choose the "Text" option from the same "Number" tab - but if you're working with data already inputted, this won't put the leading zero back in. All it'll do is keep it from disappearing, so you'd have to go back and retype the leading zero in all the affected cells...manually. Ugh.



So yes, definitely go with the ZIP code mode.

Answer 8

You can use this reverse phone lookup service ( http://reversephones.info )



It can be useful in a lot of different situations. If the caller asks for your name reply by asking for their name. Do not give them any information about yourself. Ask what number they dialed and ask for their number. You will be able to use this number later when you carry out a reverse phone lookup to identify the caller. The individuals who make these calls intend to upset you in some way or even cause you more significant harm. This could be for many different reasons. Perhaps they wish to get revenge for you breaking off a relationship or they may simply just get some sort of twisted gratification from making you feel uncomfortable. Some criminals attempt to obtain useful information from unsuspecting children. Teach your children to ask for the caller's name and number and to inform the caller that someone will call them back.

Answer 9

right click to cell / go to proporties



at the number tab convert "digit/number" to "text"

and you allowed to put zero first but excel take this input as a text and can't do mathematical process.



or you can write this with quotation mark "034560"

Answer 10

You can't. You should have right-clicked the column and chose format cells, and chose "text" as the type of category. In default, it is placed in "general".

Answer 11

I wish to ask the same question as the op.

Answer 12

I also have the same question