Extract UK postcode from cell (excel/access)?

Question

I have a database that is currently in excel 2013 but I will need this 'facility' in Access too.

A cell will contain a string that 'may' be just a postcode or it may be a region, followed by a postcode.

Some Examples:

S6 5HF
Liverpool, L6 4RT
London, SW1P 6YU
FY3 6TF, England
BS16 5TY

I need to be able to extract the postcode out of that (preferably without the space). The postcode can be in any of the following formats

LN NLL, LNN NLL, LLN NLL, LLNN NLL, LLNL NLL

where L is a capital letter and N is an integer. If it helps, the last block of 3 characters is always Number, Letter, Letter and that format is never used in the first half.

expletive_xom · Accepted Answer

theres a few ways to do this.
if you do this a lot....then VBA would work.

if you have this sample, you can do a quick text-to-columns, then copy&paste a formula
=IF(ISNUMBER( VALUE(MID(A1, LEN(A1)-2,1))), SUBSTITUTE(A1," ",""),SUBSTITUTE(B1," ",""))

heres a click by click
- copy&paste this sample in a new sheet (start at A1)
- click data
- click text-to-columns (middle of ribbon, this puts everything into its own cell)
- click delimited
- click next
- click comma
- click finish
- copy&paste this in C1

=IF(ISNUMBER( VALUE(MID(A1, LEN(A1)-2,1))), SUBSTITUTE(A1," ",""),SUBSTITUTE(B1," ",""))

should give you a postcode.
- copy C1 and paste down the column.
you can copy column c and paste to Access
or to get just the values in excel, and get rid of the formulas
- copy column C
- right click on D1
- click paste special
- click values

let me know how that works

Eliya · Answer

1

Extract UK postcode from cell (excel/access)?

2 Antworten