Match based on part of a postcode (UK)

[nickmax1]

I have a list of beginning parts of UK post codes that (ie N1, EC1 etc) that I need to match up with full post codes (N78TZ, EC14BY etc) but i need the macro to identify just the beginning parts , which are sometimes 2 or 3 digits long....how can this be achieved?

sample sheet included. See colomn A has the full post code, it needs to match that to the list in the other sheet (called PC) and in Col B return either A or B (or Other if the post code doesnt feature)

[mehmetcik]

This is easy in theory.

Use the find function to find all occurrences of your text.

then reject all those where the text isn't the leftmost part of the selected cell.

[AB33]

Try the attached.

[nickmax1]

AB33 - that is fantastic but it dosnt quite work.....try putting in the postcode SE64BY for example, and it should bring back the result of B (SE6 = B) but it brings back "Other" instead.
Strangely, if you then add WC2E9EN to the list and rerun the macro, it changes SE64BY to an A, and WC2E9EN is returned as an Other instead of an A

[XOR LX]

Hi,

Can I ask if this is in anyway related to this post? Seems like a remarkable coincidence if not, especially since the OP is asking for "So that when I paste the postcodes into another sheet along with other info excel can tell by the first 2 letters in the postcode..."

http://www.excelforum.com/excel-form...ategories.html

Regards

[AB33]

Try the attached.

[nickmax1]

ab33,

thank you for the quick response.....
but i found an error - if you put in the postcode sw197by it thinks it is a Code A (i assume because it thinks it is SW1 instead of SW19)?

[AB33]

Yes, the code works on the first match, but not the closest match.

For e.g., SE64BY, closest match is found in row 77, but the code returns row 22.

[nickmax1]

Yes, the code works on the first match, but not the closest match.

For e.g., SE64BY, closest match is found in row 77, but the code returns row 22.

So it isn't possible to get the closest match?

[AB33]

The honest answer is: I do not know. It may be possible with regular expression or a formula, but as far as VBA is concerned, even if you were to use the find and match partial function, it will return the same as instr function.

[mehmetcik]

try the following file

[nickmax1]

mehmetcik
thank you for your code - I was wondering how it works as it doesnt seem to be able to identify N19TZ (which is N1, but it classes it as a N19)

I wonder if there is some logic that can be given in the macro to help identify it. From first glance to get round my problem above, you could try to get the macro to read the length of the postcode, if its 5 alphanumeric digits then it could only be N1, of its more than 5 it has to be N19

Postcode formats can only take the following formats (where A=alphabetic and N = number)

AN NAA (N1 9TZ)
ANN NAA (N17 1NW)
AAN NAA (CR2 6XH)
AANN NAA (DN55 1PT)
ANA NAA (W1A 1HQ)
AANA NAA (EC1A 6BB)

[mehmetcik]

Hi

I don't see that.

The example I sent you works as per your request,