Yahoo Answers is shutting down on 4 May 2021 (Eastern Time) and the Yahoo Answers website is now in read-only mode. There will be no changes to other Yahoo properties or services, or your Yahoo account. You can find more information about the Yahoo Answers shutdown and how to download your data on this help page.
Extract UK postcode from cell (excel/access)?
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.
2 Answers
- expletive_xomLv 78 years agoFavourite 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
