[Fixed] Zip Codes in Excel Starting with 0

Get FREE Advanced Excel Exercises with Solutions!

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.

Steps:

• 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.

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
`=VALUE(E5)`

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.

`=TEXT(VALUE(E5), "#")`

• 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.

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.

Steps:

• 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.

Steps:

• 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

Conclusion

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.

<< Go Back toÂ Zip Code in ExcelÂ |Â Number FormatÂ |Â Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Shajratul Alam Towhid

Md Shajratul Alam Towhid, a BSc graduate in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, holds a pivotal role as an Excel & VBA Content Developer at ExcelDemy. Fueled by a deep passion for research and innovation, he actively engages with Excel. In his capacity, Towhid not only adeptly tackles complex challenges but also exhibits enthusiasm and expertise in gracefully navigating tough situations, emphasizing his unwavering commitment to consistently delivering exceptional, high-quality content that... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF