RegEX function or VBA Macro for multiple country Postcode check

[JAGIUS]

HI all,

New here so please go easy

I am wanting to create a function or query (whichever works better) which will do the following:

1. Run through a column of postcodes entered manually by the inputter and highlight entries which do not match the specified RegEx expression.
2. The regEx expression list used will be determined by which ISO country code the postcode is assigned to.

I am able to find loads of code on line for a postcode checker for one country but not seen anything for multiple countries - I assume a vlookup table may have to be combined wit the RegEx checker but cannot fathom how to implement this.

So in summary a "simple" regEx checker based on a list of postcodes BUT the regEx expressions will VARY based on which country the postcode is assigned to

Hope the above makes sense

TIA

 

[kevin9999]

You can download a list of all postal codes - all countries - from the following website:

All Postal Code - All countries (Geonames)

Once you've downloaded the list, you can create a simple list of postal codes & their associated ISO country codes. Personally, I would use it like the following brief demo (with lookup functions & conditional formatting to highlight when not found).

Book1
	A	B	C	D	E	F	G
1	ISO Code	Postal Code		List to Check	ISO Code(s)
2	FR	46612		46612	FR
3	ES	46613		46613	ES	UK	USA
4	UK	46613		511444	Unlisted
5	USA	46613		46614	FR
6	FR	46614		46615	FR
7	FR	46615
8
Sheet3

Cell Formulas
Range	Formula
E2,E4:E6,E3:G3	E2	=TRANSPOSE(FILTER($A$2:$A$7,$B$2:$B$7=D2,"Unlisted"))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:D7	Expression	=COUNTIF($B$2:$B$7,D2)=0	text	NO

 

[JAGIUS]

You can download a list of all postal codes - all countries - from the following website:

All Postal Code - All countries (Geonames)

Once you've downloaded the list, you can create a simple list of postal codes & their associated ISO country codes. Personally, I would use it like the following brief demo (with lookup functions & conditional formatting to highlight when not found).

Hi Thanks for this and yes it works fine but it requires the list of postcodes to be populated well and upto date which for certain countries the coverage is not great. What I was really after is for a RegEx check so that would cover everything.

 

[kevin9999]

Regex will give you the right pattern for a country's post codes, but it won't be able to check whether they're valid & up to date. If you have a look at the download, some countries don't have a single pattern anyway.
Anyhow, my post was merely a suggestion

 

[JAGIUS]

Regex will give you the right pattern for a country's post codes, but it won't be able to check whether they're valid & up to date. If you have a look at the download, some countries don't have a single pattern anyway.
Anyhow, my post was merely a suggestion

Yes and greatful that could come in useful but I am ideally after a pattern matching exercise for now but again thanks for the input