Postcode Extraction

[simon4amiee]

Hi guys, i sthere a way to extract a postcode when there are double space on some and not others.

SA1 1AB (i need SA1 1AB)
HU1 2AB (i need HU1 2AB)
HU17 0BG (ok)

For some reason the postcode field returns 2 spaces when the postcode length is only 6 digits and 3 spaces when the postcode length is only 5 digits. Im assuming that the postcode field needs to be 8 characters in length (including a space).

All I want to to remove the addition spaces and return a normal looking postcode!

 

[Brianwarnock]

Something like

Left(postcode,instr(postcode," ")+1) & right(postcode,3)

Untested

Brian

 

[pr2-eugin]

Could you replace the double spaces with a single space?

Replace(postCode, "  ", " ")

 

[jdraw]

See this info re UK postal codes Format

 

[Brianwarnock]

Could you replace the double spaces with a single space?

Replace(postCode, "  ", " ")

You would have to do it twice for the situation with three spaces.

Brian

 

[Brianwarnock]

See this info re UK postal codes Format

Interesting but how does it help?

Brian

 

[jdraw]

Hi Brian,

I was trying to remove some guesswork

Im assuming that the postcode field needs to be 8 characters in length (including a space).

The OP has access to the code, so if it is a restriction with his/her code/design, it should be evident what the length should be.

If the question is what are the standards for UK postal code formats, then here's a reference to work from/with.

 

[Brianwarnock]

Ok , I must admit that statement you quote puzzled me.

Brian

 

[Gasman]

Something like

Left(postcode,instr(postcode," ")+1) & right(postcode,3)

Untested

Brian

PMFJI but wouldn't Left(postcode,instr(postcode," ")) & right(postcode,3) do the job as you will have located the first space in the field.?

 

[Brianwarnock]

Erm! Erm! Can I plead a Senior Moment?

Brian

Ps what does PMFJI stand for if it's pardon me for jumping in then that is always welcome when things need correction or even for another method, nobody has exclusive rights to a thread, but your manners are most welcome .

 

[Gasman]

Of course.

And yes PMFJI means exactly that.

 

[gemma-the-husky]

i would definitely strip out multiple spaces to get (hopefully) a valid UK post code.

 while instr(postcode,"  ")>0 '2 spaces!
     postcode = replace(postcode,"  "," ")
 wend