Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Create Zip Code with Excel Formula (6 Easy Ways)

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.


Download Practice Workbook

You can download this workbook to practice yourself.


6 Easy Ways to Create Zip Code Formula in Excel

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.

excel zip code formula


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

Create a Formula to Extract Zip Codes in Excel

Steps:

  • First, type the formula:
=RIGHT(B5,5)
  • 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 all the way 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 all the way down.
  • Hence, the zip codes will appear.

Create a Formula to Extract Zip Codes in Excel

Read More: How to Remove Last 4 Digits of Zip Code in Excel (10 Easy Ways)


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.

Use Predefined Postal Code Format

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

Use Predefined Postal Code Format

Read More: How to Concatenate ZIP Codes in Excel (3 Easy Methods)


3. Create 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).

Create Custom Zip Code Format in Excel

  • 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 Format Zip Code to 5 Digits in Excel (5 Easy Methods)


4. Set Custom Format to Get Leading Characters in Zip Code

In the event that 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.

Set Custom Format to Get Leading Characters in Zip Code

  • 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 Lookup County from Zip Code in Excel


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.

Insert Excel TEXT Function to Add Leading Zeros

  • 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:
=TEXT(C5, “00000”)
  • 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.

output of zip code from TEXT funtion

Read More: How to Sort Data by Zip Code in Excel (With Easy Steps)


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

Apply LEFT Function to Replace 9-Digit Zip Code with 5-Digit

  • 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 Convert Zip Code to State with VLOOKUP in Excel


Conclusion

In conclusion, we have discussed here some easy ways to create an Excel zip code formula. Not to mention, our ExcelDemy website shows various simple problem-solving methods like this. Please feel free to leave any further queries or recommendations in the comment box below.


Related Articles

Yousuf Khan

Yousuf Khan

Hello! This is MD Yousuf Khan. I am a graduate & post-graduate in Information Technology from Jahangirnagar University, Bangladesh. Currently, I am writing articles for ExcelDemy. I am an independent, self-motivated person with enthusiasm to learn new things, and always try to do my best in any work assigned to me.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo