[Fixed] Zip Codes in Excel Starting with 0

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.

zip codes in excel starting with 0


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.

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

zip codes in excel starting with 0

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

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

Using VALUE Function

  • 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

zip codes in excel starting with 0

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

=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 Create Zip Code with Excel Formula (6 Easy Ways)


Similar Readings


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

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

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 zero will be like this.

zip codes in excel starting with 0


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 and explore our website ExcelDemy, a one-stop Excel solution provider.


Related Articles

Towhid

Towhid

Hello, myself Shajratul Alam Towhid. Basically, I am a Naval Architect who wants to expand knowledge in the field of Microsoft Excel. I wish all of my articles will be beneficial for the readers.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo