Breaking down postcodes

[Kevin Cheesman]

I need our postcode to display in the correct order. I've broken the first part down into 2 fields so I can sort on the text and numbers, but I need to sort the second part of the postcode too.

The UK postcode format is usually TTNN TNN, however then first part can consist of just one letter or just one number. There is always a space in between the two parts.

[comment]

What is "the correct order"?

[Kevin Cheesman]

What I was thinking of doing was breaking the poscode field into 4 different calculated fields. So the text field, Poscode holding BR23 7TB would become...

BR (text field)

23 (numeric field)

7 (numeric field)

TB (text field)

I could then sort on these fields to ensure the actual postcode field in sorted correctly.

[comment]

You haven't answered my question.

[Kevin Cheesman]

You haven't answered my question.

The correct order for BR23 3TU would be..

BR - ascending

23 - ascending

3 - ascending

TU - ascending

This would ensure that BR1 is not followed by BR12 but BR2 etc. and the same for the second part of the postcode.

[comment]

OK, then wouldn't it be sufficient to insert a zero after the first one or two alpha characters when they are followed by a single digit? That is "BR12 3TU" would remain unchanged, while "BR1 3TU" would be turned into "BR01 3TU".

Edited September 22, 2009 by Guest

[Kevin Cheesman]

Yes, not a problem, but what I looking for is a calculation to scan the existing postcodes and break them down.

[comment]

I am afraid I don't follow you. What would be the purpose of breaking it into four calculation fields, when it can be done by a single one? Not to mention that sorting by one field is more efficient than by four separate ones.

[Kevin Cheesman]

ok, so how do I sort on just part of a field and how do I sort the numbers when they are currently text?

[David Jondreau]

I presume what comment is getting at is that you don't need 4 additional fields. You only need one, Post Code Sort. That field will have the calculation that will return the post code in a sortable format.

It's not clear how a single letter in the first part of the code should sort, but I'd assume it should sort above any double letter. B23 sorts before BA11?

Let([

PostCode = Upper(table::field);

first = LeftWords(PostCode;1);

second = Rightwords(PostCode;1);

lets = filter(first;"ABCDEFGHIJKLMNOPQRSTUVWXYZ");

lets = Left(lets & 0;2);

nums = filter(first;"0123456789");

nums = Right(0& nums;2);

result = lets & nums & second

];

result

)

PS: Got distracted and posted later than I intended...This was in response to post #341074.

Edited September 22, 2009 by Guest

[Kevin Cheesman]

Added this calc field. It concatenates the postcode but they do not display in the correct numerical order when you sort on this field.

[comment]

Could you provide a specific example? Like, these codes sort in this order, when they should sort in this order?

[Kevin Cheesman]

Sort order is back up the thread.

The correct order for BR23 3TU would be..

BR - ascending

23 - ascending

3 - ascending

TU - ascending

This would ensure that BR1 is not followed by BR12 but BR2 etc. and the same for the second part of the postcode.

[comment]

I believe DJ's calculation should provide that. If you're seeing something else, it may be due to incorrect implementation (make sure the result type is Text) or something that I am missing. If the latter, I will keep missing it unless you show it me.

[Kevin Cheesman]

Oops! It was me. Yes, it does work.

Thanks for all your help.