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 to remove it. In this article, we’ll try to discuss the solution of zip codes in Excel starting with 0.
Download Practice Workbook
Fixing 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
We can change the Custom format to Number to eliminate leading zero. In the following dataset we want to get Zip codes without starting 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 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 zero.
Read More: How to Format Zip Codes in Excel (2 Easy Methods)
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 starting zero for Montgomery City.
- Secondly, press ENTER to get Zip code without starting 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 zero.
Read More: How to Concatenate ZIP Codes in Excel (3 Easy Methods)
3. Utilizing VALUE and TEXT Functions
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 Create Zip Code with Excel Formula (6 Easy Ways)
- How to Find ZIP Code from Address in Excel (2 Easy Ways)
- Excel Formula to Change Zip Code to State (5 Examples)
- How to Lookup ZIP Code in Excel (4 Suitable Methods)
- Sort Data by Zip Code in Excel (With Easy Steps)
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 Remove Last 4 Digits of Zip Code in Excel (10 Easy Ways)
5. Using Paste Special Command
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.
Read More: How to Format Zip Code to 5 Digits in Excel (5 Easy Methods)
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 zero will be like this.
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 and explore our website ExcelDemy, a one-stop Excel solution provider.