Format ZIP Code for Multiple Countries

mintz

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

ISOCountryFormat
AUAustralia9999
ATAustria9999
BEBelgium9999
BRBrazil99999[-999]
CACanadaA9A 9A9
DKDenmark9999
FIFinland99999
FRFrance99999
DEGermany99999
HUHungary9999
IEIrelandA9A AAAA
ILIsrael99999 99
ITItaly99999
JPJapan999-9999
LULuxembourg9999
MYMalaysia99999
MXMexico99999
NLNetherlands9999 AA
NZNew Zealand9999
NONorway9999
PLPoland99-999
PTPortugal9999-999
RURussia999999
SASaudi Arabia99999
SGSingapore999999
KRSouth Korea99999
SESweden999 99
CHSwitzerland9999
TRTurkey99999
UAUkraine99999
UKUnited KingdomAA9[9] 9AA
USUnited States99999[-9999]

<tbody>
</tbody>

A represents alphabet
9 represents number
[ ] represents optional

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

CountryZip (Input)Zip (Output)
NL1613 LC1613LC
CAJ7J1C1J7J 1C1
UKCT179PACT17 9PA
UKCM29BECM2 9BE
US7411274112
US635406354
US20735460720735-4607
US20735 460720735-4607
US20735-460720735-4607

<tbody>
</tbody>

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

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Shouldn't you have a space in the NL output?

In any case, here's a formula:

ABCDEFGHIJ
1ISOCountryFormatCountryZip (Input)Zip (Output)HelperOutput
2AUAustralia9999NL1613 LC1613LC1613LC1613 LC
3ATAustria9999CAJ7J1C1J7J 1C1J7J1C1J7J 1C1
4BEBelgium9999UKCT179PACT17 9PACT179PACT17 9PA
5BRBrazil99999[-999]UKCM29BECM2 9BECM29BECM2 9BE
6CACanadaA9A 9A9US74112741127411274112
7DKDenmark9999US6354635463546354
8FIFinland99999US20735460720735-460720735460720735-4607
9FRFrance99999US20735 460720735-460720735460720735-4607
10DEGermany99999US20735-460720735-460720735460720735-4607
11HUHungary9999IEB1C BNCDB1CBNCDB1C BNCD
12IEIrelandA9A AAAAJP12345561234556123-4556
13ILIsrael99999 99SE7654376543765 43
14ITItaly99999
15JPJapan999-9999
16LULuxembourg9999
17MYMalaysia99999
18MXMexico99999
19NLNetherlands9999 AA
20NZNew Zealand9999
21NONorway9999
22PLPoland99-999
23PTPortugal9999-999
24RURussia999999
25SASaudi Arabia99999
26SGSingapore999999
27KRSouth Korea99999
28SESweden999 99
29CHSwitzerland9999
30TRTurkey99999
31UAUkraine99999
32UKUnited KingdomAA9[9] 9AA
33USUnited States99999[-9999]
34

<tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
I2=SUBSTITUTE(SUBSTITUTE(G2," ",""),"-","")
J2=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))))

<tbody>
</tbody>

<tbody>
</tbody>



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

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
How do I go about the UDF option?
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
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)
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

Forum statistics

Threads
1,213,562
Messages
6,114,322
Members
448,564
Latest member
ED38

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