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

zip codes in excel starting with 0


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.

Changing Custom Format to Number

  • Secondly, select the copied cells > go to Home > click the drop-down button shown in the figure > select Number.

zip codes in excel starting with 0

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

zip codes in excel starting with 0

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

Here, E5 refers to the Zip code with a starting zero for Montgomery City.

Using VALUE Function

  • 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

zip codes in excel starting with 0

  • 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), "#")

Utilizing VALUE and TEXT Function

  • Secondly, press ENTER.
  • Thirdly, use the Fill Handle.
  • Eventually, the output will be like this.

zip codes in excel starting with 0

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

Applying Error Checking Option

  • Secondly, select the cells > click the error sign in the E5 cell > select Convert to Number.

zip codes in excel starting with 0

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

Steps:

  • Firstly, select a cell and give input 1
  • Secondly, copy the cell. In this case, it is H5.

Using Paste Special Command

  • Thirdly, select the cells of F5:F15 > right-click on the selected cells > choose Paste Special.

zip codes in excel starting with 0

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

zip codes in excel starting with 0

Read More: How to Remove Last 4 Digits of Zip Code in Excel


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.

Utilizing Text to Columns Wizard

  • Secondly, in the window below, choose Delimited > click Next.

zip codes in excel starting with 0

  • Thirdly, select Tab in the next window > click Next.

  • Fourthly, click General in the Column data format > give F5 cell as Destination > click Finish.

zip codes in excel starting with 0

  • Consequently, the output of Zip codes without starting at zero will be like this.

zip codes in excel starting with 0

Read More: How to Concatenate ZIP Codes in Excel


Download Practice Workbook


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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo