In this Microsoft Excel article, we verily tried to create an Excel zip code formula with 6 easy methods. In detail, Excel provides some very useful features and formulas to write zip codes for different locations. To summarize, learn these 6 easy ways to create a ZIP Code formula in Excel for your articles and workbooks.
How to Create Zip Code Formula in Excel: 6 Easy Ways
In this part, We will show you 6 easy ways to create a zip code formula in Excel. To demonstrate, we take a dataset where column B consists of various addresses. And, column C refers to the zip codes of those locations. Meanwhile, for this purpose of demonstration, I used Microsoft Excel 365. However, you can use any other version that is more convenient for you. I used the sample dataset listed below.
1. Create a Simple Formula to Extract Zip Codes in Excel
Firstly, this method will show you how to extract US zip codes from a column of addresses. As seen below, we have a list of address data with different zip code presentations; some use the shortcode version, while others use the longer code version. Provided that, we will use the RIGHT function to illustrate this method. RIGHT gives out the last character or characters in a text string, according to your specification.
Steps:
- First, type the formula:
- In the [num-chars], we will write 5 in order to get 5-digit zip codes.
- Finally, hit enter.
Momentarily, the zip code will show up. Up next, we will use the Autofill function to get the other zip codes.
- Now, copy the formula cell.
- After that, drag it down to the first 5 columns.
- Thus, the zip codes will appear.
Once we get the 5-digit zip codes, we will try to extract the 10-digit zip codes in the same fashion.
- Again, execute the formula:
=RIGHT(B11,10)- Alternatively, type 10 as a value in the [num-chars] box.
- Then, hit enter.
- Correspondingly, a 10-digit zip code will appear.
- Similarly, copy the formula cell down.
- Hence, the zip codes will appear.
Read More: How to Lookup ZIP Code in Excel
2. Use Predefined Postal Code Format
On this occasion, we will use a predefined postal code format. Zip Code and Zip Code + 4 are two postal code special number formats in Excel. Thus, use any one of these to get your desired results.
Steps:
- To begin with, select the cells you want to format.
- Here, (B5:B9) is selected.
- Now, go to the Home tab.
- On the Home tab, click the Dialogue Box Launcher next to the Number.
- As a result, a dialogue box will appear.
- Next, in the Category box, tap Special.
- After that, click Zip code or Zip Code +4 in the Type box.
- Lastly, hit OK.
Read More: How to Lookup County from Zip Code in Excel
3. Create a Custom Zip Code Format in Excel
For this method, we will use a custom zip code format. Excel has two postal code special number formats: Zip Code and Zip Code + 4 as a predefined zip code format. If this doesn’t work for you, subsequently create your own custom code format. Following this, a custom zip code format is used here to easily understand.
Steps:
- First, select the range (B5:B9).
- Then, go to the Home tab.
- Click the Dialogue Box Launcher next to Number.
As a result, a dialogue box will appear.
- In the Category box, tap Custom.
- After that, in the Type box, select the number format you wish to customize.
- At last, hit OK.
Read More: How to Convert Zip Code to State with VLOOKUP in Excel
4. Set Custom Format to Get Leading Characters in Zip Code
If you want the postal code to be preceded by enough characters to fit the width of the cell, you can format a cell or range of cells to display leading characters. Given these points, we will set a custom format to get leading characters in the zip code.
Steps:
- Firstly, select the cells you want to format.
- Now, go to the Home bar.
- Click the Dialogue Box Launcher next to Number.
- Thereupon, a dialogue box will appear.
- Now, in the Category box, tap Custom.
- After that, in the Type box, type *0.
- Then, type the format you wish to use.
- For instance, for a 5-digit postal code, type *0#####.
- Lastly, hit OK.
Read More: How to Map Excel Data by ZIP Code
5. Insert Excel TEXT Function to Add Leading Zeros
In general, try to use the TEXT function for zip codes, social security numbers, zip codes, product ID numbers, and employee ID numbers. To summarize, you should use the TEXT function to convert a numeric value to a specified format as needed. Therefore, before writing any zip codes, define your input to Text. Also, in this method, we will show you how to add leading zeroes. Leading zeros refer to the input 0 that gives the zip codes an equal number of characters. For this demonstration, we took a dataset that has an unequal number of characters of zip codes.
Steps:
- In the beginning, select the cells you want to customize.
- Now, go to the Home tab.
- Next, click the General Box Launcher as shown below.
- Consequently, a Dropbox will appear.
- Scroll down and then tap Text.
- As a result, you will get the desired format.
- Consequently, type your desired zip codes.
- The up-left green buttons verify the inputs as text values.
As you can see, the zip codes in column C are not the same number of characters. We will try to sort them into specific characters by adding leading zeros.
- For that purpose, type this formula:
- For instance, to get a 5-digit zip code, we input 00000 in the format_text.
- Subsequently, the desired result will show up.
- Lastly, copy the formula cell all the way down.
Read More: Excel Formula to Change Zip Code to State
6. Apply the LEFT Function to Replace 9-digit Zip Code with 5-digit
Suppose, a dataset of 9-digit zip codes is given. Now, we have to replace it with a 5-digit zip code. To emphasize, you should use the LEFT function to get the result easily. One of the functions in Excel’s TEXT category is the LEFT function. This function returns a predetermined amount of characters starting at the beginning of the provided text string.
Steps:
- Firstly, type the formula:
=LEFT(C5,5)- Then, hit enter.
- As a consequence of this, you will get the desired zip code.
- Finally, copy the formula cell all the way down.
- Hence, the 5-digit zip code will appear.
Read More: How to Auto Populate Zip Codes in Excel
Download Practice Workbook
You can download this workbook to practice yourself.
Conclusion
In conclusion, we have discussed here some easy ways to create an Excel zip code formula. Please feel free to leave any further queries or recommendations in the comment box below.
