In Sheet1 I have the following table of zip code formats:
<tbody>
</tbody>
A represents alphabet
9 represents number
[ ] represents optional
In Sheet2 I have the following input zip codes and the expected output:
<tbody>
</tbody>
How do I convert the input string to a valid zip code based on the corresponding format?
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] |
<tbody>
</tbody>
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 |
<tbody>
</tbody>
How do I convert the input string to a valid zip code based on the corresponding format?
Last edited: