Remove last 3 digits from UK postcode

[#1]

I would like to import postcode field in a table but only import the first section of the UK postcode.

UK postcodes begin with either 1 or two letters, have a single or double digit number and then end with a number and 2 letters. E.g:

LN NLL or LLN NLL or LLNN NLL or LNN NLL (they won't always have a space between).

I'm only interested in inputting the first characters (everything before the last 3 digits - LN, LLN, LLNN, LNN).

Is there a way of doing this as a calculated field when loading a table?

[marcus_sommer]

Could you give an example on real postcodes?

- Marcus

[sunny_talwar]

May be try this:

LOAD Trim(Left(PostCodes, Len(PostCodes) - 3)) as PostCodes

[awhitfield · Accepted solution]

Hi Simon,

try out:

LOAD Left(PostCode, Len(PostCode)-3) as PostCode

FROM

(ooxml, embedded labels, table is Sheet1);

Andy

[MK_QSL]

If there is no space between the first and second part of post code, there is no way we can achieve the exact output.

In case of space we can use

SubField(PostCode,' ',1) as FirstPartofPostCode

[#6]

Works a treat, I tweaked it and put it into calculated Field expression.

Thanks for your help everybody - really appreciate it.

[sunny_talwar]

You might want to consider adding Trim in there to make sure you don't have the extra white space at the end in those scenarios where there is a space between the code and 3 following letters