Format ZIP Code for Multiple Countries

M

mintz

Board Regular
Joined
Aug 5, 2015
Messages
129
In Sheet1 I have the following table of zip code formats:

[TABLE="width: 259"]
<tbody>[TR]
[TD]ISO[/TD]
[TD]Country[/TD]
[TD]Format[/TD]
[/TR]
[TR]
[TD]AU[/TD]
[TD]Australia[/TD]
[TD]9999[/TD]
[/TR]
[TR]
[TD]AT[/TD]
[TD]Austria[/TD]
[TD]9999[/TD]
[/TR]
[TR]
[TD]BE[/TD]
[TD]Belgium[/TD]
[TD]9999[/TD]
[/TR]
[TR]
[TD]BR[/TD]
[TD]Brazil[/TD]
[TD]99999[-999][/TD]
[/TR]
[TR]
[TD]CA[/TD]
[TD]Canada[/TD]
[TD]A9A 9A9[/TD]
[/TR]
[TR]
[TD]DK[/TD]
[TD]Denmark[/TD]
[TD]9999[/TD]
[/TR]
[TR]
[TD]FI[/TD]
[TD]Finland[/TD]
[TD]99999[/TD]
[/TR]
[TR]
[TD]FR[/TD]
[TD]France[/TD]
[TD]99999[/TD]
[/TR]
[TR]
[TD]DE[/TD]
[TD]Germany[/TD]
[TD]99999[/TD]
[/TR]
[TR]
[TD]HU[/TD]
[TD]Hungary[/TD]
[TD]9999[/TD]
[/TR]
[TR]
[TD]IE[/TD]
[TD]Ireland[/TD]
[TD]A9A AAAA[/TD]
[/TR]
[TR]
[TD]IL[/TD]
[TD]Israel[/TD]
[TD]99999 99[/TD]
[/TR]
[TR]
[TD]IT[/TD]
[TD]Italy[/TD]
[TD]99999[/TD]
[/TR]
[TR]
[TD]JP[/TD]
[TD]Japan[/TD]
[TD]999-9999[/TD]
[/TR]
[TR]
[TD]LU[/TD]
[TD]Luxembourg[/TD]
[TD]9999[/TD]
[/TR]
[TR]
[TD]MY[/TD]
[TD]Malaysia[/TD]
[TD]99999[/TD]
[/TR]
[TR]
[TD]MX[/TD]
[TD]Mexico[/TD]
[TD]99999[/TD]
[/TR]
[TR]
[TD]NL[/TD]
[TD]Netherlands[/TD]
[TD]9999 AA[/TD]
[/TR]
[TR]
[TD]NZ[/TD]
[TD]New Zealand[/TD]
[TD]9999[/TD]
[/TR]
[TR]
[TD]NO[/TD]
[TD]Norway[/TD]
[TD]9999[/TD]
[/TR]
[TR]
[TD]PL[/TD]
[TD]Poland[/TD]
[TD]99-999[/TD]
[/TR]
[TR]
[TD]PT[/TD]
[TD]Portugal[/TD]
[TD]9999-999[/TD]
[/TR]
[TR]
[TD]RU[/TD]
[TD]Russia[/TD]
[TD]999999[/TD]
[/TR]
[TR]
[TD]SA[/TD]
[TD]Saudi Arabia[/TD]
[TD]99999[/TD]
[/TR]
[TR]
[TD]SG[/TD]
[TD]Singapore[/TD]
[TD]999999[/TD]
[/TR]
[TR]
[TD]KR[/TD]
[TD]South Korea[/TD]
[TD]99999[/TD]
[/TR]
[TR]
[TD]SE[/TD]
[TD]Sweden[/TD]
[TD]999 99[/TD]
[/TR]
[TR]
[TD]CH[/TD]
[TD]Switzerland[/TD]
[TD]9999[/TD]
[/TR]
[TR]
[TD]TR[/TD]
[TD]Turkey[/TD]
[TD]99999[/TD]
[/TR]
[TR]
[TD]UA[/TD]
[TD]Ukraine[/TD]
[TD]99999[/TD]
[/TR]
[TR]
[TD]UK[/TD]
[TD]United Kingdom[/TD]
[TD]AA9[9] 9AA[/TD]
[/TR]
[TR]
[TD]US[/TD]
[TD]United States[/TD]
[TD]99999[-9999][/TD]
[/TR]
</tbody>[/TABLE]

A represents alphabet
9 represents number
[ ] represents optional

In Sheet2 I have the following input zip codes and the expected output:

[TABLE="width: 300"]
<tbody>[TR]
[TD]Country[/TD]
[TD]Zip (Input)[/TD]
[TD]Zip (Output)[/TD]
[/TR]
[TR]
[TD]NL[/TD]
[TD]1613 LC[/TD]
[TD]1613LC[/TD]
[/TR]
[TR]
[TD]CA[/TD]
[TD]J7J1C1[/TD]
[TD]J7J 1C1[/TD]
[/TR]
[TR]
[TD]UK[/TD]
[TD]CT179PA[/TD]
[TD]CT17 9PA[/TD]
[/TR]
[TR]
[TD]UK[/TD]
[TD]CM29BE[/TD]
[TD]CM2 9BE[/TD]
[/TR]
[TR]
[TD]US[/TD]
[TD]74112[/TD]
[TD]74112[/TD]
[/TR]
[TR]
[TD]US[/TD]
[TD]6354[/TD]
[TD]06354[/TD]
[/TR]
[TR]
[TD]US[/TD]
[TD]207354607[/TD]
[TD]20735-4607[/TD]
[/TR]
[TR]
[TD]US[/TD]
[TD]20735 4607[/TD]
[TD]20735-4607[/TD]
[/TR]
[TR]
[TD]US[/TD]
[TD]20735-4607[/TD]
[TD]20735-4607[/TD]
[/TR]
</tbody>[/TABLE]

How do I convert the input string to a valid zip code based on the corresponding format?
 
Last edited:

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
Shouldn't you have a space in the NL output?

In any case, here's a formula:

ABCDEFGHIJ
ISOCountryFormatCountryZip (Input)Zip (Output)HelperOutput
AUAustraliaNL1613 LC1613LC1613LC1613 LC
ATAustriaCAJ7J1C1J7J 1C1J7J1C1J7J 1C1
BEBelgiumUKCT179PACT17 9PACT179PACT17 9PA
BRBrazil99999[-999]UKCM29BECM2 9BECM29BECM2 9BE
CACanadaA9A 9A9US
DKDenmarkUS
FIFinlandUS20735-460720735-4607
FRFranceUS20735 460720735-460720735-4607
DEGermanyUS20735-460720735-460720735-4607
HUHungaryIEB1C BNCDB1CBNCDB1C BNCD
IEIrelandA9A AAAAJP123-4556
ILIsrael99999 99SE765 43
ITItaly
JPJapan999-9999
LULuxembourg
MYMalaysia
MXMexico
NLNetherlands9999 AA
NZNew Zealand
NONorway
PLPoland99-999
PTPortugal9999-999
RURussia
SASaudi Arabia
SGSingapore
KRSouth Korea
SESweden999 99
CHSwitzerland
TRTurkey
UAUkraine
UKUnited KingdomAA9[9] 9AA
USUnited States99999[-9999]

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]9999[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]9999[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]9999[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]74112[/TD]
[TD="align: right"]74112[/TD]
[TD="align: right"]74112[/TD]
[TD="align: right"]74112[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]9999[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]6354[/TD]
[TD="align: right"]6354[/TD]
[TD="align: right"]6354[/TD]
[TD="align: right"]6354[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]99999[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]207354607[/TD]

[TD="align: right"]207354607[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]99999[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]207354607[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]99999[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]207354607[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]9999[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]1234556[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1234556[/TD]

[TD="align: center"]13[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]76543[/TD]
[TD="align: right"][/TD]
[TD="align: right"]76543[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]99999[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]

[TD="align: right"]9999[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]

[TD="align: right"]99999[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]

[TD="align: right"]99999[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]

[TD="align: right"]9999[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]

[TD="align: right"]9999[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]24[/TD]

[TD="align: right"]999999[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]25[/TD]

[TD="align: right"]99999[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]26[/TD]

[TD="align: right"]999999[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]27[/TD]

[TD="align: right"]99999[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]28[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]29[/TD]

[TD="align: right"]9999[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]30[/TD]

[TD="align: right"]99999[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]31[/TD]

[TD="align: right"]99999[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]32[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]33[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]34[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]I2[/TH]
[TD="align: left"]=SUBSTITUTE(SUBSTITUTE(G2," ",""),"-","")[/TD]
[/TR]
[TR]
[TH]J2[/TH]
[TD="align: left"]=IF(OR(F2={"BR","US"}),IF(LEN(I2)<6,I2,REPLACE(I2,6,0,"-")),IF(F2="UK",LEFT(I2,LEN(I2)-3)&" "&RIGHT(I2,3),IFERROR(REPLACE(I2,FIND("-",VLOOKUP(F2,$A$2:$C$33,3,0)),0,"-"),IFERROR(REPLACE(I2,FIND(" ",VLOOKUP(F2,$A$2:$C$33,3,0)),0," "),I2))))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



It works for all your examples, and a few more I tried. The thing to remember though is that whenever you have manually entered data, it's VERY difficult to come up with a formula (or even a VBA solution) that can handle every possibility. I'm sure this formula will fail in some cases.

Note that the formula handles the 3 countries with optional data separately. If it's not one of those countries, it looks for a dash and inserts it at the right place. If no dash, it looks to see if it needs to add a space. If no dash or space, it just display the zip as is.

It would probably be easier to write a UDF that performs this kind of thing - it would be much easier to do validation of the zip (A=alpha, 9=number). This formula does not do that, just looks for the place to insert a space or dash.
 
Last edited:
Upvote 0
Eric W said:
Shouldn't you have a space in the NL output?

It would probably be easier to write a UDF that performs this kind of thing - it would be much easier to do validation of the zip (A=alpha, 9=number). This formula does not do that, just looks for the place to insert a space or dash.
Click to expand...

Yep, just a typo for NL, should be "1613LC" and "1613 LC" respectively
How do I go about the UDF option? I believe it will be easier to handle by first removing all space and dash from the input, then UPPERCASE, leaving just A-Z and 0-9, and then process "clean" input?
 
Upvote 0
mintz said:
How do I go about the UDF option?
Click to expand...
Here is one way...
Code: 
[table="width: 500"]
[tr]
	[td]Function ZipCodes(ByVal Zip As String, Country As String) As String
  Dim TestPattern As String, FormatPattern As String
  Zip = UCase(Replace(Replace(Zip, " ", ""), "-", ""))
  Select Case UCase(Country)
    Case "AU", "AT", "BE", "DK", "HU", "LU", "NZ", "NO", "CH"
      TestPattern = "####"
      FormatPattern = "@@@@"
    Case "FI", "FR", "DE", "IT", "MY", "MX", "SA", "KR", "TR", "UA"
      TestPattern = "#####"
      FormatPattern = "@@@@@"
    Case "RU", "SG"
      TestPattern = "######"
      FormatPattern = "@@@@@@"
    Case "SE"
      TestPattern = "#####"
      FormatPattern = "@@@ @@"
    Case "NL"
      TestPattern = "####[A-Z][A-Z]"
      FormatPattern = "@@@@ @@"
    Case "PL"
      TestPattern = "#####"
      FormatPattern = "@@-@@@"
    Case "IL"
      TestPattern = "#######"
      FormatPattern = "@@@@@ @@"
    Case "BR"
      If Len(Zip) = 5 Then
        TestPattern = "#####"
        FormatPattern = "@@@@@"
      Else
        TestPattern = "########"
        FormatPattern = "@@@@@-@@@"
      End If
    Case "US"
      If Len(Zip) = 5 Then
        TestPattern = "#####"
        FormatPattern = "@@@@@"
      Else
        TestPattern = "#########"
        FormatPattern = "@@@@@-@@@@"
      End If
    Case "PT"
      TestPattern = "#######"
      FormatPattern = "@@@@-@@@"
    Case "JP"
      TestPattern = "#######"
      FormatPattern = "@@@-@@@@"
    Case "CA"
      TestPattern = "[A-Z]#[A-Z]#[A-Z]#"
      FormatPattern = "@@@ @@@"
    Case "IE"
      TestPattern = "[A-Z]#[A-Z][A-Z][A-Z][A-Z][A-Z]"
      FormatPattern = "@@@ @@@@"
    Case "UK"
      If Len(Zip) = 6 Then
        TestPattern = "[A-Z][A-Z]##[A-Z][A-Z]"
        FormatPattern = "@@@ @@@"
      Else
        TestPattern = "[A-Z][A-Z]###[A-Z][A-Z]"
        FormatPattern = "@@@@ @@@"
      End If
  End Select
  If Zip Like TestPattern Then ZipCodes = Format(Zip, FormatPattern)
End Function[/td]
[/tr]
[/table]
 
Upvote 0
mintz said:
Just tested couple of inputs, is it possible to validate (or correct) the length of the input? (e.g. US 1234 would be converted to 01234)
Click to expand...
See if this one works correctly for you...
Code: 
[table="width: 500"]
[tr]
	[td]Function ZipCodes(ByVal Zip As String, Country As String) As String
  Dim TestPattern As String, FormatPattern As String
  Zip = UCase(Replace(Replace(Zip, " ", ""), "-", ""))
  Select Case UCase(Country)
    Case "AU", "AT", "BE", "DK", "HU", "LU", "NZ", "NO", "CH"
      TestPattern = "####"
      FormatPattern = "@@@@"
    Case "FI", "FR", "DE", "IT", "MY", "MX", "SA", "KR", "TR", "UA"
      TestPattern = "#####"
      FormatPattern = "@@@@@"
    Case "RU", "SG"
      TestPattern = "######"
      FormatPattern = "@@@@@@"
    Case "SE"
      TestPattern = "#####"
      FormatPattern = "@@@ @@"
    Case "NL"
      TestPattern = "####[A-Z][A-Z]"
      FormatPattern = "@@@@ @@"
    Case "PL"
      TestPattern = "#####"
      FormatPattern = "@@-@@@"
    Case "IL"
      TestPattern = "#######"
      FormatPattern = "@@@@@ @@"
    Case "BR"
      If Len(Zip) = 5 Then
        TestPattern = "#####"
        FormatPattern = "@@@@@"
      Else
        TestPattern = "########"
        FormatPattern = "@@@@@-@@@"
      End If
    Case "US"
      If Len(Zip) < 6 Then
        TestPattern = "#####"
        FormatPattern = "@@@@@"
      Else
        TestPattern = "#########"
        FormatPattern = "@@@@@-@@@@"
      End If
    Case "PT"
      TestPattern = "#######"
      FormatPattern = "@@@@-@@@"
    Case "JP"
      TestPattern = "#######"
      FormatPattern = "@@@-@@@@"
    Case "CA"
      TestPattern = "[A-Z]#[A-Z]#[A-Z]#"
      FormatPattern = "@@@ @@@"
    Case "IE"
      TestPattern = "[A-Z]#[A-Z][A-Z][A-Z][A-Z][A-Z]"
      FormatPattern = "@@@ @@@@"
    Case "UK"
      If Len(Zip) = 6 Then
        TestPattern = "[A-Z][A-Z]##[A-Z][A-Z]"
        FormatPattern = "@@@ @@@"
      Else
        TestPattern = "[A-Z][A-Z]###[A-Z][A-Z]"
        FormatPattern = "@@@@ @@@"
      End If
  End Select
  If Not Zip Like "*[!0-9]*" Then
    Zip = Right(String(Len(TestPattern), "0") & Zip, Len(TestPattern))
  End If
  If Zip Like TestPattern Then ZipCodes = Format(Zip, FormatPattern)
End Function[/td]
[/tr]
[/table]
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top