If you are looking for Zip codes in Excel starting with 0, then you are in the right place. During statistical analysis or research activities, we often need to use Excel to express Zip codes. Sometimes, we want zip codes without leading to zero. But we face difficulties in removing it. In this article, we’ll try to discuss the solution of zip codes in Excel starting with 0.
Excel offers various ways to eliminate leading zero from Zip codes. We just need to follow some simple steps in every method. Suppose, we have made a dataset that has column headers as City, State, Abbreviation, and Zip Code. We need to eliminate zero from the cells of Column E.
1. Changing Custom Format to Number to Fix Zip Codes Starting with Zero in Excel
We can change the Custom format to Number to eliminate the leading zero. In the following dataset, we want to get Zip codes without starting at 0.
- Firstly, copy and paste the cells of E5:E15 to cells F5:F15.
- Secondly, select the copied cells > go to Home > click the drop-down button shown in the figure > select Number.
- Eventually, the output values will be without starting at zero but with decimal values.
- To solve this, select the cells > choose Home > click the button in the Number box twice to eliminate double decimal values.
Consequently, we’ll get the Zip codes without starting at zero.
Read More: How to Find ZIP Code from Address in Excel
2. Using VALUE Function
We can use the VALUE function to eliminate starting zero from Zip codes. The VALUE function mainly takes the valid numbers to give an output.
- Firstly, write the formula in the F5
Here, E5 refers to the Zip code with a starting zero for Montgomery City.
- Secondly, press ENTER to get the Zipcode without starting with zero.
- Thirdly, use the Fill Handle by dragging down the cursor while holding the right-bottom corner of the F5
- Eventually, all the outputs will be without starting at zero.
Read More: How to Sort Data by Zip Code in Excel
3. Utilizing VALUE and TEXT Functions to Fix Zip Codes Starting with Zero
Excel also offers the combination of VALUE and TEXT functions to eliminate starting zero.
To use the formula, firstly, write the formula in the F5 cell like this.
- Secondly, press ENTER.
- Thirdly, use the Fill Handle.
- Eventually, the output will be like this.
Read More: How to Format Zip Codes in Excel
4. Applying Error Checking Option
We can also apply an error-checking option to eliminate the starting zero.
- To do this, firstly, copy and paste the E5:E15 cells to F5:F15
- Secondly, select the cells > click the error sign in the E5 cell > select Convert to Number.
- Eventually, the Zip codes will be without the starting zero.
Read More: How to Format Zip Code to 5 Digits in Excel
5. Using Paste Special Command to Fix Zip Codes Starting with Zero in Excel
Using the Paste Special command is also another solution to eliminate starting zero from Zip codes.
- Firstly, select a cell and give input 1
- Secondly, copy the cell. In this case, it is H5.
- Thirdly, select the cells of F5:F15 > right-click on the selected cells > choose Paste Special.
- A Paste Special window will appear.
- Fourthly, select All in the Paste box > choose Multiply in the Operation box > click OK.
- Eventually, we’ll get the Zip codes without starting zero like this.
6. Utilizing Text to Columns Wizard
Additionally, utilizing Text to Columns wizards is also an easy solution. We need to follow the steps below.
- Firstly, select the cell range from Column E > click Data > select Text to Columns.
- Secondly, in the window below, choose Delimited > click Next.
- Thirdly, select Tab in the next window > click Next.
- Fourthly, click General in the Column data format > give F5 cell as Destination > click Finish.
- Consequently, the output of Zip codes without starting at zero will be like this.
Read More: How to Concatenate ZIP Codes in Excel
Download Practice Workbook
That’s all about today’s session. And these are the ways for the solution of Zip codes starting with zero. We strongly believe this article would be highly beneficial for you. Don’t forget to share your thoughts and queries in the comments section.