Extract postcode from string - No other filtering

[RichS]

I have tried to find this on the forum but it seems all the answers are to do with manipulating the strings based on what the postcode is whereas I just want to extract the postcode as a separate string as simply as possible.

Basically, I have a text field called Add5 which has the last line of the address including the postcode.

Example: "Northampton NN1 7PQ"

I am trying to end up with two strings like this

Add5 = "Northampton"
Postcode = "NN1 7PQ"

Can somebody help me with this? As I only need to do this in one place, I don't think it needs a module.

Thanks

 

[Minty]

Ouch that will prove a little troublesome.

You would normally search for the first space then everything after that is the postcode, until you live in Stoke on Trent or Tunbridge Wells or....

You will have to return everything after the second last space in your string, and that also assumes that every postcode has been correctly stored with the space in it.

Edit - this code will help you on the way to you goal
http://support.microsoft.com/KB/168836

Double Edit - Even more helpful on this very forum : http://www.access-programmers.co.uk/forums/showthread.php?t=138393

 

[RichS]

Thanks for your reply Minty. Some of that might help but it still seems a little complicated.

One thing that will hopefully help is that the postcode will ALWAYS start after the second-to-last space as the string has already been formatted by it's source. Therefore, I just need the best way to find the position in the string that second-to-last space is so I can just split it from that point...

 

[CJ_London]

you could use the split function e.g.

 dim SArr() as string
  
 sarr=split(address," ")
 postcode=sarr(ubound(sarr)-2) & " " & sarr(ubound(sarr)-1)

 

[RichS]

Because of the way I am arriving at the string I need to split, I may have to approach it with a different method. Basically, the background is that the whole address is imported from another program. All the lines of the address are separated by commas, so I have code to split the address into it's separate lines as strings with the exception of the postcode which is only separated with spaces, hence my quest to extract it another way. However, because of the fact that the address can be variable in the number of lines it creates, the last line is never fixed as a particular string name.

I'm thinking now that it may be an idea to replace the second-from-last space with a comma and then let my split routine run which will then place the postcode into a separate line as with the other lines. Any suggestions how I do this?

 

[RichS]

UPDATE!

I have sorted it by doing things the other way round... I found the second-from-last space in the whole address string and replaced it with a comma. I then used my routine to split the resulting string into lines using the commas.

Thanks for your support that helped me to cure this headache!