Pulling out Postcode from Address?

[blairscott]

Hello all,

First post:

I've been tasked with the long and laborious task of pulling out the postcodes of over 2000 addresses in excel. I'm trying to find a formula to help me with this. now the hard part is that the postcode doesn't always appear in the same position in the address.

for example one might read:
T.J. MORRIS LTD
AXIS BUSINESS PARK
EAST LANCS ROAD
GILLMOSS, LIVERPOOL
L11 0JA
UNITED KINGDOM

and another might be
BOOKER CASH & CARRY LTD
DC BRANCH NUMBER - 676
C/O GREGORY DISTRIBUTION LIMITED
UNIT 1, HIGHWOOD ROAD
PATCHWAY
BRISTOL
BS34 5DJ


Can anyone help with this? is this even possible? One think i was thinking is that most end with the postcode then United kingdom could there be away to pull out this ?

Regards

 

[InfoH]

do these address all appear in 1 cell or across multiple cells?

for example is it:

CELL A1: BOOKER CASH & CARRY LTD
DC BRANCH NUMBER - 676
C/O GREGORY DISTRIBUTION LIMITED
UNIT 1, HIGHWOOD ROAD
PATCHWAY
BRISTOL
BS34 5DJ

or:

A1: BOOKER CASH & CARRY LTD
A2: DC BRANCH NUMBER - 676
A3: C/O GREGORY DISTRIBUTION LIMITED
A4: UNIT 1, HIGHWOOD ROAD
A5: PATCHWAY
A6: BRISTOL
A7: BS34 5DJ

?

 

[blairscott]

Apologies they are all in the same cell.

 

[InfoH]

thats a shame.. lol
on different lines or all across the 1 line?

I would say you could sort text to columns and then filter each column and delete anything which isnt a postcode, still fairly mainly but no where near as manual as it could be

 

[blairscott]

i've pulled out the last two lines of the address and am just sorting it from there. still manual but as you say alot better than what it could have been

thanks

 

[shobolos]

=iferror(if(and((or(len(a1)=8,len(a1)=9)),(find(" ",a1))),a1,""),"")