international postal codes

[JJ Johnson]

Every month I need to validate internation postal codes for acceptable
formats in an Excel 2003 spreadsheet. Some countries have numerous
acceptable formats (up to about 20 or so for some) using letters and numbers
and sometimes the initials of the country. For example:

l = letter
n = number

Finland
nnnnn
FI nnnnn
FI-nnnnn
FInnnnn
FIN nnnnn
FIN-nnnnn
FINnnnnn

Canada
lnl nln
lnl-nln
lnlnln

Is there a function in Excel 2003 that can do this kind of validation?

 

[Bernard Liengme]

Actually the Canada Post like to see LNL NLN with the space
best wishes

 

[Ross]

Hi JJ,

Firstly you would need to collate the various international postal code
exmaples.

Once you have these you can continue to write VBA code and use IF to verify
the country and postal code format. Add a comment in another column.

For example;
Lets say for this example you have "Canada" entered in cell "A1" and the
exmaple postal code of "A1A 2B2" in cell "B1" in your spreadsheet.

Open the Visual Basic for Applications Editor (Alt-F11) and enter/copy the
following code;

Sub checkPostalCodes()

'Declare Variables
Dim CountryRegion As String
Dim PostalCode As String

'Assign Spreadsheet reference
CountryRegion = Range("A1")
PostalCode = Range("B1")

'Use if function to check postal code is correct
If CountryRegion = "Canada" And PostalCode Like "[A-Z][0-9][A-Z]
[0-9][A-Z][0-9]" Then
'if true
Range("C1") = "OK"
Else
'if false
Range("C1") = "Error"
End If

'Finish
End Sub

All you would have to do know is use a loop to process through all your
records/rows of data. I hope this helps, and if you want more information
on Postal Codes and Macros then visit the MSDN web address below;

http://msdn2.microsoft.com/en-us/library/bb177324.aspx

Cheers

Ross

 

[Ross]

Hi JJ,

Sorry I forgot to add the "variations" to that code. See below, it will
also test the different variations for Canada;
lnl nln
lnl-nln
lnlnln

Sub checkPostalCodes()

'Declare Variables
Dim CountryRegion As String
Dim PostalCode As String

'Assign Spreadsheet reference
CountryRegion = Range("A1")
PostalCode = Range("B1")

'Use if function to check postal code is correct
If CountryRegion = "Canada" And PostalCode Like "[A-Z][0-9][A-Z]
[0-9][A-Z][0-9]" Then
'various if true
Range("C1") = "OK"
ElseIf CountryRegion = "Canada" And PostalCode Like
"[A-Z][0-9][A-Z]-[0-9][A-Z][0-9]" Then
Range("C1") = "OK"
ElseIf CountryRegion = "Canada" And PostalCode Like
"[A-Z][0-9][A-Z][0-9][A-Z][0-9]" Then
Range("C1") = "OK"
Else
'if false
Range("C1") = "Error"
End If

'Finish
End Sub

Hope this helps

Cheers

Ross