Excel: Format ZIP Code for Multiple Countries


In Sheet1 I have the following table of zip code formats:

ISO Country Format
AU Australia 9999
AT Austria 9999
BE Belgium 9999
BR Brazil 99999[-999]
CA Canada A9A 9A9
DK Denmark 9999
FI Finland 99999
FR France 99999
DE Germany 99999
HU Hungary 9999
IE Ireland A9A AAAA
IL Israel 99999 99
IT Italy 99999
JP Japan 999-9999
LU Luxembourg 9999
MY Malaysia 99999
MX Mexico 99999
NL Netherlands 9999 AA
NZ New Zealand 9999
NO Norway 9999
PL Poland 99-999
PT Portugal 9999-999
RU Russia 999999
SA Saudi Arabia 99999
SG Singapore 999999
KR South Korea 99999
SE Sweden 999 99
CH Switzerland 9999
TR Turkey 99999
UA Ukraine 99999
UK United Kingdom AA9[9] 9AA
US United States 99999[-9999]
A represents alphabet 9 represents number [ ] represents optional In Sheet2 I have the following input zip codes and the expected output:
Country Zip (Input) Zip (Output)
NL 1613 LC 1613LC
CA J7J1C1 J7J 1C1
UK CT179PA CT17 9PA
UK CM29BE CM2 9BE
US 74112 74112
US 6354 06354
US 207354607 20735-4607
US 20735 4607 20735-4607
US 20735-4607 20735-4607
How do I convert the input string to a valid zip code based on the corresponding format?


This question generated 8 answers. To proceed to the answers, click here.

This thread is current as of January 30, 2018.


For more resources for Microsoft Excel