Format ZIP Code for Multiple Countries

mintz

Board Regular
Joined
Aug 5, 2015
Messages
117
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:

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,328
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:

mintz

Board Regular
Joined
Aug 5, 2015
Messages
117
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?
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,400
Office Version
  1. 2010
Platform
  1. Windows
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]
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,400
Office Version
  1. 2010
Platform
  1. Windows
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]
 

Watch MrExcel Video

Forum statistics

Threads
1,112,817
Messages
5,542,666
Members
410,567
Latest member
SCraig123
Top