Extract UK post code from a single column filled with addresses

[JackDanIce]

Hi,

I have a column which contains UK addresses, collected from users via a website submission form.

As such, loads of input issues without any structure to the post code (lower case, 7 or 8 digits long, sometimes with a space in between, other times no post code provided at all), example of redacted data:

Book1
	N
1	Address
2	4 captain's field llanfrynach
3	Maida Vale
4	jgljglj
5	22 Osprey AVE. Westhoughton,Bolton by
6	5 north lodge Chester le street dh34ba
7	gfbdxvzfxvcsdfvfdv
8	42 gawsworth close
9	Padang
10	16 Huntsmead
11	jgljglj
12	15 Main Street Ledston Wf102aa
13	Chikamagalur
14	9285 solstivve
15	5 tanton road
16	Maida Vale
17	109 Rushton Grove Harlow Essex CM17 9QW
18	165 knightsbridge
19	12 Gough Square
20	95 Midanbury Lane bitterne Southampton SO18 4HA
21	76Jubilee way Rogerstone Newport NP10 9NL
22	Flat4 2Sorrell street hulme machester M155SR
23	32 Roman Road
24	Flat4 2Sorrell street hulme machester M155SR
25	93 Ruby Street
26	16 the spinney
27	53 Talbot Ave Kingswood Bristol BS15 1HE
28	28 Avon Hockley TAMWORTH Staffordshire B77 5QA
29	37A The Avenue, Stockton-on-Tees, TS19 7EP
30	10 Mansell Rd Shoreham west sussex
31	Rosemead, High St, Clotton, Tarporley, Cheshire, CW60EG
32	55 abbeystead avenue L301pn
33	Hazrich Ironworks Road, Tow Law, Bishop Auckland, Co. Durham DL13 4EQ
34	19 Greenleigh Close Bolton BL17BQ
35	19 Greenleigh Close Bolton BL17BQ
36	19 Greenleigh Close Bolton BL17BQ
37	16 Lucerne Way Harold Hill Rm3 8BS
38	Woodlea
39	25 Cheshire road Birmingham b6 7bx
40	21 lonsdale avenue
41	137 prince of wales road
42	75 thornbarrow road windermere cumbria la232dq
43	The Brae, Port William
44	14 North Road, Alconbury Weston, Huntingdon, PE28 4JR
45	17 nelson road wouldham kent
46	38 doncaster vroad
Data

How can I extract the post code only? Formula or Power Query solutions preferrred.

TIA,
Jack

 

[JackDanIce]

Or VBA with RegEx (not sure of syntax), with patterns to try:

"%r{[A-Z]{1,2}[0-9R][0-9A-Z]? [0-9][A-Z]{2}}i"

"/^(([A-Z][A-Z]{0,1})([0-9][A-Z0-9]{0,1})) {0,}(([0-9])([A-Z]{2}))$/i"