Did you consider a calculated field like; _cOutbound = getvalue ( substitute ( postcode ; space ; pilcrow ) ; 1 ) to get the outbound component of the postcode, then sort by that? It may need a little more than stated in case the postcode was incomplete or badly keyed, or was for an address in a country which has a more logical zip coding than we endure in the UK. But in principal, I think that would do what you require!
So in full my calculation would be: _cPostcodeOutbound = GetValue ( Substitute ( Postcode ; " " ; " ") ; 1)
What would you put in the fields you've titled (space) and (pilcrow) ?
The 'space' would be just that; " " (double inverted commas, space, double inverted commas).
The one entitled pilcrow is a little more complex; 'pilcrow' is the the name for the 'return' character; its in the calculation operators in the Script Workspace; looks like a reversed uppercase 'P'. I believe that you don't always need to enclose it in inverted commas, but I always do.
It would be prudent to check that there is at least one space in case its a non-UK zip code in which case don't do the truncation; so a better calculation might be:
Let ( cleaned = upper ( trim ( postcode ) ) ; if ( patterncount ( cleaned ; space ) > 0 ; getvalue ( substitute ( cleaned ; space ; pilcrow ) ; 1 ) ; postcode ) )
I hope that's a bit clearer? I'll bet some of the results will be weird to start with - usually this may be down to picnic (PICNIC - Wiktionary)...
Let me know how you get on!
Just a slight heads up, that works fine with the space in the right place. The outward code, the bit you want is everything bar the last three characters (the inward code) so you could try:
postcode = table::yourPostcodeField
Trim(Left(Postcode; length(postcode) - 3))
All you need to do is swop out the table::yourPostcodeField for the one in your solution
That might fix the PICNIC issue.......